01.分區(qū)表性能

PostgreSQL 對分區(qū)表的支持由來已久。在 10.0 之前，分區(qū)表需要用戶通過繼承的方式手動創(chuàng)建，從 10.0 開始支持聲明式分區(qū)，即通過 SQL 直接創(chuàng)建分區(qū)表，改善了分區(qū)表的易用性；在 11 中，支持 HASH 分區(qū)，并在計劃和執(zhí)行階段，增強分區(qū)裁剪策略，提升分區(qū)表查詢性能；PostgreSQL 12 進一步增強了分區(qū)表的查詢和數(shù)據(jù)導入性能，尤其對分區(qū)數(shù)量多的場景，查詢優(yōu)化效果尤為顯著。

在阿里云創(chuàng)建兩個同等規(guī)格（4c8g）的 RDS PostgreSQL 11 和 12 的實例，測試不同分區(qū)數(shù)情況下，使用 COPY 導入 1 億行數(shù)據(jù)的性能對比如下。可見，隨著分區(qū)數(shù)增多，導入性能始終優(yōu)于 PostgreSQL 11。COPY 導入數(shù)據(jù)的性能提升得益于在 12 中支持了分區(qū)表批量插入，在次之前，僅支持一次一行的插入模式。

對于查詢操作，在 PostgreSQL 10 中，會依次檢查每個分區(qū)表，判斷其可能有滿足條件的數(shù)據(jù)，每個分區(qū)表的處理與普通表的處理流程類似；PostgreSQL 11 引入了分區(qū)裁剪特性，可以更早地定位需要訪問的分區(qū)；PostgreSQL 12 則近一步將分區(qū)裁剪功能前置，避免為每個分區(qū)加載元數(shù)據(jù)并生成相應的內部結構，使得查詢計劃耗時進一步與無關的分區(qū)解耦。

由此可見，該優(yōu)化與查詢條件的分區(qū)過濾性相關，分區(qū)過濾性越好，所需處理的分區(qū)越少，優(yōu)化效果越好。

不同分區(qū)數(shù)下，分區(qū)鍵（同時也是主鍵）上的查詢性能對比如下?？梢?，分區(qū)數(shù)越多，PostgreSQL 12 的性能提升越明顯，最高提升達 150 倍。而隨著分區(qū)數(shù)增加，PostgreSQL 12 的性能則保持相對穩(wěn)定。

雖然分區(qū)表性能有大幅提升，但與單表相比，在很多場景下性能還有一定差距，在做表結構設計時，仍然需要結合實際業(yè)務場景，選擇是否分區(qū)以及分區(qū)數(shù)量。

02.索引增強

B-tree 索引被廣泛應用于數(shù)據(jù)庫系統(tǒng)中，可以有效減少查詢需要訪問的數(shù)據(jù)量，提升查詢性能。索引是一種 "空間換時間" 的查詢優(yōu)化策略，本身也會占用一些存儲空間，其性能對查詢也至關重要。

PostgreSQL 12 提升了標準 B-tree 的整體性能，減少了磁盤空間占用，對于復合索引，其空間使用率最多可減少 40%，可以有效節(jié)省用戶的磁盤空間；對于有重復項的 B-tree 索引，其性能也有所提升。另外，引入 REINDEX CONCURRENTLY 命令，用戶可以在業(yè)務無感知的情況下重建索引。

我們通過測試直觀感受一下 B-tree 索引的空間占用優(yōu)化。分別在 PostgreSQL 11 和 12 中創(chuàng)建如下表和索引，并插入 2000 萬行數(shù)據(jù)，VACUUM 更新統(tǒng)計信息。

*請左右滑動閱覽 

CREATE TABLE foo (  
    aid bigint NOT NULL,  
    bid bigint NOT NULL  
);  
ALTER TABLE foo  
    ADD CONSTRAINT foo_pkey PRIMARY KEY (aid, bid);  
CREATE INDEX foo_bid_idx ON foo(bid);  
INSERT INTO foo (aid, bid)  
    SELECT i, i / 10000 
     FROM generate_series(1, 20000000) AS i;   
VACUUM (ANALYZE) foo;   

分別查看兩個 PostgreSQL 版本中 foo_bid_idx 索引的大小，如下：

*請左右滑動閱覽 

# PostgreSQL 11  
postgres=> \di+ foo_bid_idx  
                                    List of relations  
 Schema |    Name     | Type  |    Owner    | Table | Persistence |  Size  | Description  
--------+-------------+-------+-------------+-------+-------------+--------+-------------  
 public | foo_bid_idx | index |   postgres  | foo   | permanent   | 544 MB |  
(1 row)  
# PostgreSQL 12  
postgres=> \di+ foo_bid_idx  
                                    List of relations  
 Schema |    Name     | Type  |    Owner    | Table | Persistence |  Size  | Description  
--------+-------------+-------+-------------+-------+-------------+--------+-------------  
 public | foo_bid_idx | index |   postgres  | foo   | permanent   | 408 MB |  
(1 row) 

可見，PostgreSQL 11 的索引比 PostgreSQL 12 大 33%，在索引較多的場景下，如此大幅度的空間節(jié)省還是很可觀的。

除 B-tree 索引外，其他索引也有增強。如減小生成 GiST、GIN 和 SP-GiST 索引的WAL日志的開銷，支持用 GiST 創(chuàng)建覆蓋索引，支持用 SP-GiST 索引的 distance 運算符執(zhí)行 K-NN 查詢等。

03.支持 SQL/JSON 路徑語言（path language）

PostgreSQL 在之前的版本中就已經支持了 JSON 數(shù)據(jù)類型，并支持對簡單 JSON 數(shù)據(jù)的查詢操作，如果 JSON 數(shù)據(jù)比較復雜，如嵌套較多，包含數(shù)組等，則不能便捷地查詢其中的值，往往需要依賴外部插件來實現(xiàn)，比如支持 SQL/JSON 路徑語言 的 jsquery 插件。

PostgreSQL 12 對非結構化數(shù)據(jù)的支持再進一步。內置支持了 SQL 2016 標準引入的 JSON 特性和豐富的路徑查詢方法，引入新的數(shù)據(jù)類型 jsonpath 表示路徑表達式（path expression），支持 JSON 上的各種復雜查詢，不再依賴插件。具體的使用方法可以參考文檔，在此不贅述。

04.參數(shù)控制 Prepared 計劃

對于重復執(zhí)行的 PREPARE 語句，PostgreSQL 會緩存其執(zhí)行計劃，執(zhí)行 PREPARE 語句時，PostgreSQL 會自動選擇是重新生成一個新的計劃（通常稱之為定制計劃，custom plan），還是使用緩存的計劃（即通用計劃，generic plan），但在特定場景下，數(shù)據(jù)庫的選擇可能并不是最優(yōu)的。PostgreSQL 12 為用戶提供了一個參數(shù) plan_cache_mode 來自主選擇使用哪種計劃，比如查詢的參數(shù)如果總是固定的常量，則可以顯式設置該參數(shù)，使優(yōu)化器總是使用通用計劃，避免 SQL 解析和重寫的代價，從而優(yōu)化查詢性能。

執(zhí)行PREPARE 并運行，前 5 次均使用定制計劃：

*請左右滑動閱覽 

postgres=> prepare p(integer) as select aid from foo where aid=$1;  
PREPARE  
postgres=> EXPLAIN EXECUTE p(1);  
                               QUERY PLAN  
------------------------------------------------------------------------- 
  Index Only Scan using foo_pkey on foo  (cost=0.44..1.56 rows=1 width=8)  
   Index Cond: (aid = 1)  
(2 rows)  
# 后續(xù)四次執(zhí)行的結果在此省略 

執(zhí)行第 6 次時使用通用計劃，如下：

*請左右滑動閱覽 

postgres=> EXPLAIN EXECUTE p(1);  
                               QUERY PLAN  
-------------------------------------------------------------------------  
 Index Only Scan using foo_pkey on foo  (cost=0.44..1.56 rows=1 width=8)  
   Index Cond: (aid = $1)  
(2 rows) 

重新執(zhí)行  PREPARE，并設置 plan_cache_mode 為  force_generic_plan，觀察計劃使用情況，可見第 1 次執(zhí)行時就會使用通用計劃，而無需等到第 6 次執(zhí)行。

*請左右滑動閱覽 

postgres=> DEALLOCATE p;  
DEALLOCATE  
postgres=> prepare p(integer) as select aid from foo where aid=$1;  
PREPARE  
# plan_cache_mode 設置為 force_generic_plan  
postgres=> set plan_cache_mode = force_generic_plan;  
SET  
postgres=> EXPLAIN EXECUTE p(1);  
                               QUERY PLAN  
-------------------------------------------------------------------------  
 Index Only Scan using foo_pkey on foo  (cost=0.44..1.56 rows=1 width=8)  
   Index Cond: (aid = $1)  
(2 rows) 

是否使用通用計劃可以通過執(zhí)行計劃中變量是否做了參數(shù)化處理來判斷。

05.可插拔表存儲接口

一直以來，PosgreSQL 都只支持 heap 表這一種存儲引擎，其實現(xiàn)與其他模塊耦合較多。PostgreSQL 12 借鑒自身索引可擴展的實現(xiàn)方式，抽象出一層存儲引擎訪問接口，為后續(xù)支持多種存儲引擎奠定了基礎，如 ZHeap、列存、K/V 存儲、內存引擎等。

可插拔表存儲訪問接口的架構如下，在原有架構基礎上，增加了 表訪問管理層（Table Access Manager），提供統(tǒng)一的表訪問接口，不同的存儲引擎只需實現(xiàn)該接口即可接入。

目前，存儲引擎仍然只支持 Heap 表，相信不久的將來會支持更多的存儲引擎。感興趣的讀者也可以嘗試自行實現(xiàn)一個存儲引擎。

*請左右滑動閱覽 

postgres=> select * from pg_am;  
 oid  | amname |      amhandler       | amtype  
------+--------+----------------------+--------  
    2 | heap   | heap_tableam_handler | t  
  403 | btree  | bthandler            | i  
  405 | hash   | hashhandler          | i  
  783 | gist   | gisthandler          | i  
 2742 | gin    | ginhandler           | i  
 4000 | spgist | spghandler           | i  
 3580 | brin   | brinhandler          | i 
 (7 rows) 

06.豐富的插件支持

阿里云 RDS PostgreSQL 12 提供了更加豐富的插件支持，滿足廣大用戶在一些垂直領域和特殊場景下的需求，以下介紹一些較常用、有趣的插件，更多支持插件可以參考 PostgreSQL 的支持插件列表。

• roaringbitmap 將 roaringbitmap 作為一種內置數(shù)據(jù)類型，提供豐富的函數(shù)支持，使用 Roaring Bitmap 算法，極大提升位圖計算性能。

• RDKit 支持 mol 數(shù)據(jù)類型（描述分子類型）和 fp 數(shù)據(jù)類型（描述分子指紋），支持化學分子計算和化學分子檢索等功能。

• Ganos 阿里云自研時空數(shù)據(jù)引擎，支持對空間/時間數(shù)據(jù)進行高效的存儲、索引、查詢和分析計算。

• PASE 高性能向量檢索插件，使用業(yè)界成熟穩(wěn)定且高效的 ANN（Approximate nearest neighbor）檢索算法，包括 IVFFlat 和HNSW 算法，通過這兩種算法，可以在 PostgreSQL 數(shù)據(jù)庫中實現(xiàn)極高速向量查詢。

• zhparser 中文分詞插件，助力實現(xiàn)中文的全文檢索。

• oss_fdw 使用該插件可以將 OSS 中的數(shù)據(jù)加載到 PostgreSQL 中，也支持將 PostgreSQL 中的數(shù)據(jù)寫入 OSS 中。

07.總結

RDS PostgreSQL 12 無論功能和性能都有很大提升，包括分區(qū)表查詢性能優(yōu)化，B-tree 索引空間優(yōu)化和性能提升，參數(shù)方式選擇 Prepare 語句執(zhí)行計劃，內置的、功能全面的 SQL/JSON 路徑語言和更加豐富的插件支持?？刹灏伪碓L問接口作為未來支持多存儲引擎的基礎，意義重大，目前仍然只支持 Heap 表，用戶測暫時不會有感知。

除本文介紹的特性外，該版本還有很多其他特性，如多列 MCV（Most-Common-Value）統(tǒng)計，內聯(lián) CTE（Common table expressions）等，文中未及介紹，感興趣的讀者可以參考相關文獻，點擊閱讀原文阿里云購買實例進行體驗。

08.參考文獻

•  https://www.postgresql.org/about/press/presskit12/
•  https://www.postgresql.org/docs/12/release-12.html
•  https://www.postgresql.org/docs/12/functions-json.html#FUNCTIONS-SQLJSON-PATH
•  https://www.postgresql.org/docs/12/tableam.html