背景
《PostgreSQL 覆寫 Oracle 18c 重大新特性》Oracle 19c 新特性摘自蓋老師
《Oracle 19c 新特性及官方文檔搶鮮下載下傳》文章，其中有一些特性在PostgreSQL中很早以前已經支援。本文旨在介紹PG如何使用這些特性。
1.Data Guard 備庫DML自動重定向
在使用 ADG 作為備庫進行讀寫分離部署時，可能因為應用的原因，會有偶然的DML操作發送到備庫上，在 19c 中，Oracle 支援自動重定向備庫 DML，具體執行步驟為：
更新會自動重定向到主庫；
主庫執行更新、産生和發送Redo日志到備庫；
在Redo備庫應用後，ADG會話會透明的看到更新資訊的落地實施；
這一特性可以通過在系統級或者會話級設定參數 ADG_REDIRECT_DML 參數啟用，通過這種方式，ADG 會話的 ACID 一緻性得以保持，同時透明的支援『多數讀，偶爾更新』應用的自然讀寫分離配置。
這個特性的引入，将進一步的增加 ADG 的靈活性，幫助使用者将備庫應用的更加充分。
PostgreSQL 如何支援
1 修改核心支援
PostgreSQL standby與primary通信采用流複制協定
https://www.postgresql.org/docs/11/protocol-replication.html如果要讓PG支援隻讀從庫轉發DML到上遊節點，首先需要協定層支援。
digoal@pg11-test-> psql
psql (11.1)
Type "help" for help.
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
postgres=# create table a (id int);
ERROR: cannot execute CREATE TABLE in a read-only transaction
postgres=# \set VERBOSITY verbose
postgres=# create table a (id int);
ERROR: 25006: cannot execute CREATE TABLE in a read-only transaction
LOCATION: PreventCommandIfReadOnly, utility.c:246
postgres=# insert into a values (1);
ERROR: 25006: cannot execute INSERT in a read-only transaction
LOCATION: PreventCommandIfReadOnly, utility.c:246 目前寫操作報錯，判定為如下SQL請求類型時，直接報錯。
/*
* check_xact_readonly: is a utility command read-only?
*
* Here we use the loose rules of XactReadOnly mode: no permanent effects
* on the database are allowed.
*/
static void
check_xact_readonly(Node *parsetree)
{
/* Only perform the check if we have a reason to do so. */
if (!XactReadOnly && !IsInParallelMode())
return;
/*
* Note: Commands that need to do more complicated checking are handled
* elsewhere, in particular COPY and plannable statements do their own
* checking. However they should all call PreventCommandIfReadOnly or
* PreventCommandIfParallelMode to actually throw the error.
*/
switch (nodeTag(parsetree))
{
case T_AlterDatabaseStmt:
case T_AlterDatabaseSetStmt:
case T_AlterDomainStmt:
case T_AlterFunctionStmt:
case T_AlterRoleStmt:
case T_AlterRoleSetStmt:
case T_AlterObjectDependsStmt:
case T_AlterObjectSchemaStmt:
case T_AlterOwnerStmt:
case T_AlterOperatorStmt:
case T_AlterSeqStmt:
case T_AlterTableMoveAllStmt:
case T_AlterTableStmt:
case T_RenameStmt:
case T_CommentStmt:
case T_DefineStmt:
case T_CreateCastStmt:
case T_CreateEventTrigStmt:
case T_AlterEventTrigStmt:
case T_CreateConversionStmt:
case T_CreatedbStmt:
case T_CreateDomainStmt:
case T_CreateFunctionStmt:
case T_CreateRoleStmt:
case T_IndexStmt:
case T_CreatePLangStmt:
case T_CreateOpClassStmt:
case T_CreateOpFamilyStmt:
case T_AlterOpFamilyStmt:
case T_RuleStmt:
case T_CreateSchemaStmt:
case T_CreateSeqStmt:
case T_CreateStmt:
case T_CreateTableAsStmt:
case T_RefreshMatViewStmt:
case T_CreateTableSpaceStmt:
case T_CreateTransformStmt:
case T_CreateTrigStmt:
case T_CompositeTypeStmt:
case T_CreateEnumStmt:
case T_CreateRangeStmt:
case T_AlterEnumStmt:
case T_ViewStmt:
case T_DropStmt:
case T_DropdbStmt:
case T_DropTableSpaceStmt:
case T_DropRoleStmt:
case T_GrantStmt:
case T_GrantRoleStmt:
case T_AlterDefaultPrivilegesStmt:
case T_TruncateStmt:
case T_DropOwnedStmt:
case T_ReassignOwnedStmt:
case T_AlterTSDictionaryStmt:
case T_AlterTSConfigurationStmt:
case T_CreateExtensionStmt:
case T_AlterExtensionStmt:
case T_AlterExtensionContentsStmt:
case T_CreateFdwStmt:
case T_AlterFdwStmt:
case T_CreateForeignServerStmt:
case T_AlterForeignServerStmt:
case T_CreateUserMappingStmt:
case T_AlterUserMappingStmt:
case T_DropUserMappingStmt:
case T_AlterTableSpaceOptionsStmt:
case T_CreateForeignTableStmt:
case T_ImportForeignSchemaStmt:
case T_SecLabelStmt:
case T_CreatePublicationStmt:
case T_AlterPublicationStmt:
case T_CreateSubscriptionStmt:
case T_AlterSubscriptionStmt:
case T_DropSubscriptionStmt:
PreventCommandIfReadOnly(CreateCommandTag(parsetree));
PreventCommandIfParallelMode(CreateCommandTag(parsetree));
break;
default:
/* do nothing */
break;
}
} 2 修改核心支援
利用fdw，讀寫操作重新向到FDW表（fdw為PostgreSQL的外部表，可以重定向到主節點）
例如
create rule r1 as on insert to a where pg_is_in_recovery() do instead insert into b values (NEW.*); 這個操作需要一個前提，核心層支援standby可寫FDW表。
并且這個方法支援的SQL語句有限，方法1更加徹底。
3 citus插件，所有節點完全對等，所有節點均可讀寫資料庫
《PostgreSQL sharding : citus 系列7 - topn 加速(count(*) group by order by count(*) desc limit x
2.Oracle Sharding 特性的多表家族支援
在Oracle Sharding特性中，被分片的表稱為 Sharded table，這些sharded table的集合稱為表家族（Table Family），表家族之中的表具備父-子關系，一個表家族中沒有任何父表的表叫做根表（root table），每個表家族中隻能有一個根表。表家族中的所有Sharded table都按照相同的sharding key(主鍵)來分片。
在12.2，在一個SDB中隻支援一個表家族，在 19c 中，SDB 中允許存在多個表家族，每個通過不同的 Sharding Key進行分片，這是 Sharding 特性的一個重要增強，有了 Multiple Table Families 的支援，Sharding 才可能找到更多的應用場景。
PostgreSQL sharding支援非常豐富：
1、plproxy
《PostgreSQL 最佳實踐 - 水準分庫(基于plproxy)》 《阿裡雲ApsaraDB RDS for PostgreSQL 最佳實踐 - 4 水準分庫(plproxy) 之 節點擴充》 《阿裡雲ApsaraDB RDS for PostgreSQL 最佳實踐 - 3 水準分庫(plproxy) vs 單機 性能》 《阿裡雲ApsaraDB RDS for PostgreSQL 最佳實踐 - 2 教你RDS PG的水準分庫(plproxy)》 《ZFS snapshot used with PostgreSQL PITR or FAST degrade or PG-XC GreenPlum plproxy MPP DB's consistent backup》 《A Smart PostgreSQL extension plproxy 2.2 practices》 《使用Plproxy設計PostgreSQL分布式資料庫》2、citus
count(*) group by order by count(*) desc limit x
3、pg-xl
https://www.postgres-xl.org/4、antdb
https://github.com/ADBSQL/AntDB5、sharding sphere
http://shardingsphere.apache.org/6、乘數科技出品勾股資料庫，使用fdw支援sharding
7、pg_pathman+FDW支援sharding
https://github.com/postgrespro/pg_shardman 《PostgreSQL 9.5+ 高效分區表實作 - pg_pathman》3.透明的應用連續性支援增強
在Oracle RAC叢集中，支援對于查詢的自動切換，當一個節點失效，轉移到另外一個節點，在19c中，Oracle 持續改進和增強了連續性保持，資料庫會自動記錄會話狀态，捕獲用于重演的資訊，以便在切換時，在新節點自動恢複事務，使DML事務同樣可以獲得連續性支援：
在事務送出後自動禁用狀态捕獲，因為送出成功的事務将不再需要在會話級恢複；
在事務開始時，自動重新啟用狀态跟蹤；
要将一個會話内的請求轉移到另一個節點，需要支援同樣的快照視角，否則會出現查詢不一緻的情況。PostgreSQL支援快照的導出，分析給其他會話，使得所有會話可以處于同一視角。
《PostgreSQL 共享事務快照功能 - PostgreSQL 9.2 can share snapshot between multi transactions》這個技術被應用在：
1、并行一緻性邏輯備份
2、會話一緻性的讀寫分離
《PostgreSQL 10.0 preview 功能增強 - slave支援WAITLSN 'lsn', time;用于設定安全replay栅欄》為了能夠支援透明應用連續性，1、可以在SQL中間層支援(例如為每個會話建立快照，記錄快照資訊，轉移時在其他節點建立連接配接并導入快照)，2、SQL驅動層支援，3、也可以在核心層支援轉移。
會增加一定的開銷。
4.自動化索引建立和實施
對于關系型資料庫來說，索引是使得查詢加速的重要手段，而如何設計和建立有效的索引，長期以來是一項複雜的任務。
在 Oracle 19c 中，自動化索引建立和實施技術被引入進來，Oracle 通過模拟人工索引的思路，建立了内置的專家系統。
資料庫内置的算法将會通過捕獲、識别、驗證、決策、線上驗證、監控的全流程管控索引自動化的過程。
這一特性将會自動幫助使用者建立有效的索引，并通過提前驗證確定其性能和有效性，并且在實施之後進行監控，這一特效将極大緩解資料庫索引維護工作。
自動化還将删除由新建立的索引（邏輯合并）廢棄的索引，并删除自動建立但長時間未使用的索引。
1、EDB PPAS版本，支援自動建議索引
《PostgreSQL 商用版本EPAS(阿裡雲ppas(Oracle 相容版)) 索引推薦功能使用》2、PG社群版本，根據統計資訊，top SQL，LONG SQL等資訊，自動建立索引
《PostgreSQL SQL自動優化案例 - 極簡，自動推薦索引》 《自動選擇正确索引通路接口(btree,hash,gin,gist,sp-gist,brin,bitmap...)的方法》PG 虛拟索引
《PostgreSQL 索引虛拟列 - 表達式索引 - JOIN提速》 《PostgreSQL 虛拟|虛假 索引(hypothetical index) - HypoPG》PostgreSQL 優勢
PG 支援9種索引接口(btree, hash, gin, gist, spgist, brin, bloom, rum, zombodb)，同時PG支援索引接口擴充，支援表達式索引，支援partial索引。以支援各種複雜業務場景。
5.多執行個體并行重做日志應用增強
在Oracle Data Guard環境中，備庫的日志應用速度一直是一個重要挑戰，如果備庫不能夠及時跟上主庫的步調，則可能影響備庫的使用。
自Oracle 12.2 版本開始，支援多執行個體并行應用，這極大加快了恢複進度，在 18c 中，開始支援 In-Memory 列式存儲，在 19c 中，并行應用開始支援 In-Memory列式存儲。
對于邏輯從庫，支援一對一，一對多，多對一，多對多的部署方法。PG 邏輯訂閱每個通道一個worker process，可以通過建立多個訂閱通道來實作并行。
對于實體從庫，異步STANDBY的WAL APPLY延遲通常是毫秒級。
6.Oracle的混合分區表支援
在 19c 中，Oracle 增強了分區特性，可以将外部對象存儲上的檔案，以外部表的方式連結到分區中，形成混合分區表，借助這個特性，Oracle 将資料庫内外整合打通，冷資料可以剝離到外部存儲，熱資料在資料庫中線上存儲。
這個特性借助了外部表的特性實作，以下是一個示例：
CREATE TABLE orders ( order_idnumber,
order_dateDATE, … )
EXTERNAL PARTITION ATTRIBUTES
( TYPE oracle_loaderDEFAULTDIRECTORY data_dir
ACCESS PARAMETERS (..) REJECT LIMIT unlimited)
PARTITION BY RANGE(order_date)
( partition q1_2015 values less than(‘2014-10-01’)
EXTERNAL LOCATION (‘order_q1_2015.csv’),
partition q2_2015 values less than (‘2015-01-01’),
partition q3_2015 values less than (‘2015-04-01’),
partition q4_2015 values less than (‘2015-07-01’)); PostgreSQL 的fdw為外部存儲（可以是外部任意資料源，包括檔案，DB，WWW，S3，OSS等）。
使用PG繼承技術，即可完成分區的混合存儲（本地存儲，外部存儲混合），甚至SHARDING。
《ApsaraDB的左右互搏(PgSQL+HybridDB+OSS) - 解決OLTP+OLAP混合需求》 《PostgreSQL 9.6 sharding based on FDW & pg_pathman》7.線上維護操作增強
在不同版本中，Oracle 持續增強線上維護操作，例如在 12.2 開始支援的Online Move、線上修改普通表為分區表等特性。
在19c 中，持續增強了智能的、細粒度的遊标失效控制，将DDL操作對于遊标失效的影響降至最低，例如，在 19c 中，comment on table的操作，将不會引起遊标的失效。
針對分區維護的操作，例如Truncate分區等，Oracle 将進行細粒度的控制，和DDL操作無關的SQL将不受DDL失效影響。
PostgreSQL 設計之初就支援了DDL事務，可以将DDL與DML混合在一個事務中處理。
begin;
insert into tbl values (...);
drop table xx;
create table xx;
alter table xx;
insert xx;
end; 又例如切換表名，可以封裝為一個事務。
另外對于普通表轉分區表，可以這樣操作：
《PostgreSQL 普通表線上轉換為分區表 - online exchange to partition table》8.自動的統計資訊管理
随着表資料的變化，優化器表資料統計資料将近實時重新整理，以防止次優執行計劃
統計的線上維護内置于直接路徑加載操作中
當資料顯着變化時運作自動統計資訊收集作業，例如。，自上次收集統計資訊以來，表中超過10％的行被添加/更改
第一個看到需要重新編譯SQL遊标的會話（例如，由于新的優化器統計資訊）執行重新編譯
其他會話繼續使用舊的SQL遊标，直到編譯完成
避免因重新編譯而導緻大量會話停頓
PostgreSQL autovacuum 設計之初就是采用的動态統計資訊收集，并且支援到了叢集、TABLE級别可設定，使用者可以根據不同表的負載情況，設定自動收集統計資訊的門檻值。
相關參數
autovacuum_analyze_scale_factor
autovacuum_analyze_threshold
autovacuum_naptime
autovacuum_max_workers
autovacuum_work_mem 同時統計資訊的柱狀圖個數支援動态設定到表、叢集級。
表級設定
alter table xx SET STATISTICS to xx;
相關參數
default_statistics_target 9.自動化的SQL執行計劃管理
在 19c 中，資料庫預設的就會啟用對于所有可重用SQL的執行計劃捕獲（當然SYS系統Schema的SQL除外），然後進行自動的執行計劃評估，評估可以針對AWR中的TOP SQL、SGA、STS中的SQL進行。
如果被評估的執行計劃優于目前執行計劃（一般是要有效率 50%以上的提升），會被加入到執行計劃基線庫中，作為後續的執行選擇，而不佳的執行計劃則會被标記為不可接受。
有了這個特性，SQL執行計劃的穩定性将更進一步。
PostgreSQL 自适應執行計劃插件AQO，支援類似功能。對于複雜SQL尤為有效。
https://github.com/postgrespro/aqoAdaptive query optimization is the extension of standard PostgreSQL cost-based query optimizer. Its basic principle is to use query execution statistics for improving cardinality estimation. Experimental evaluation shows that this improvement sometimes provides an enormously large speed-up for rather complicated queries.
10.SQL功能的增強
在 19c 中，SQL 功能獲得了進一步的增強，這其中包括對于 COUNT DISTINCT的進一步優化，在12c中引入的近似 Distinct 操作已經可以為特定SQL帶來極大性能提升，現在基于位圖的COUNT DISTINCT 操作繼續為查詢加速。
除此之外，LISTAGG 增加了 DISTINCT 關鍵字，用于對操作資料的排重。
ANY_VALUE 提供了從資料組中獲得随機值的能力，如果你以前喜歡用 Max / Min 實作類似的功能，新功能将顯著帶來效率的提升。ANY_VALUE 函數在 MySQL 早已存在，現在應該是 Oracle 借鑒和參考了 MySQL 的函數做出的增強。
PostgreSQL 支援近似聚合，支援流計算，支援聚合中的排序，支援自定義聚合函數等。
1、使用hyperloglog插件，PostgreSQL可以實作機率計算，包括count distinct的機率計算。
https://github.com/citusdata/postgresql-hll 《PostgreSQL hll (HyperLogLog) extension for "State of The Art Cardinality Estimation Algorithm" - 3》 《PostgreSQL hll (HyperLogLog) extension for "State of The Art Cardinality Estimation Algorithm" - 2》 《PostgreSQL hll (HyperLogLog) extension for "State of The Art Cardinality Estimation Algorithm" - 1》[《[轉]流資料庫 機率計算概念 - PipelineDB-Probabilistic Data Structures & Algorithms》](
https://github.com/digoal/blog/blob/master/201801/20180116_01.md)2、TOP-N插件
https://github.com/citusdata/cms_topn3、pipelinedb 插件
《PostgreSQL 流計算插件 - pipelinedb 1.x 參數配置介紹》 《PostgreSQL pipelinedb 流計算插件 - IoT應用 - 實時軌迹聚合》通過流計算，适應更多的實時計算場景。
小結
PostgreSQL是一款非常優秀的企業級開源資料庫，不僅有良好的Oracle相容性，同時在Oracle面前也有很大更加優秀的地方：
插件化，可擴充(包括類型、索引接口、函數、操作符、聚合、視窗、FDW、存儲過程語言（目前支援plpgsql,plsql,c,pljava,plperl,pltcl,pllua,plv8,plpython,plgo,...幾乎所有程式設計語言的存儲過程），采樣，...)。
如何從O遷移到PG：
《xDB Replication Server - PostgreSQL, MySQL, Oracle, SQL Server, PPAS 全量、增量(redo log based, or trigger based)同步（支援single-master, mult-master同步, 支援DDL）》 《MTK使用 - PG,PPAS,oracle,mysql,ms sql,sybase 遷移到 PG, PPAS (支援跨版本更新)》 《ADAM，從Oracle遷移到PPAS，PG的可視化評估、遷移産品》混合使用情況下的資源隔離管理
《PostgreSQL 商用版本EPAS(阿裡雲ppas(Oracle 相容版)) HTAP功能之資源隔離管理 - CPU與刷髒資源組管理》參考
http://www.sohu.com/a/294160243_505827PostgreSQL 許願連結
您的願望将傳達給PG kernel hacker、資料庫廠商等, 幫助提高資料庫産品品質和功能, 說不定下一個PG版本就有您提出的功能點. 針對非常好的提議，獎勵限量版PG文化衫、紀念品、貼紙、PG熱門書籍等，獎品豐富，快來許願。
開不開森.
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)