01.分區(qū)表性能

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

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

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

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

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

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

02.索引增強(qiáng)

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

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

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

*請(qǐng)左右滑動(dò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;   

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

*請(qǐng)左右滑動(dòng)閱覽 

# 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%，在索引較多的場(chǎng)景下，如此大幅度的空間節(jié)省還是很可觀的。

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

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

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

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

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

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

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

*請(qǐng)左右滑動(dòng)閱覽 

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í)行的結(jié)果在此省略 

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

*請(qǐng)左右滑動(dòng)閱覽 

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，并設(shè)置 plan_cache_mode 為  force_generic_plan，觀察計(jì)劃使用情況，可見第 1 次執(zhí)行時(shí)就會(huì)使用通用計(jì)劃，而無需等到第 6 次執(zhí)行。

*請(qǐng)左右滑動(dòng)閱覽 

postgres=> DEALLOCATE p;  
DEALLOCATE  
postgres=> prepare p(integer) as select aid from foo where aid=$1;  
PREPARE  
# plan_cache_mode 設(shè)置為 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) 

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

05.可插拔表存儲(chǔ)接口

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

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

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

*請(qǐng)左右滑動(dòng)閱覽 

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 提供了更加豐富的插件支持，滿足廣大用戶在一些垂直領(lǐng)域和特殊場(chǎng)景下的需求，以下介紹一些較常用、有趣的插件，更多支持插件可以參考 PostgreSQL 的支持插件列表。

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

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

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

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

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

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

07.總結(jié)

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

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

08.參考文獻(xiàn)

•  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