作者：淩策 世界上幾乎最強大的開源數據庫系統 PostgreSQL，於 2019 年 10 月 3 日發佈了 12 版本，該版本已經在阿里雲正式發佈。PostgreSQL 12 在功能和性能上都有很大提升，如大分區表高併發性能提升百倍，B-tree 索引空間和性能優化，實現 SQL 2016 標準的 JSON 特性，支持多列 MCV（Most-Common-Value）統計，內聯 CTE（Common table expressions）以及可插拔的表存儲訪問接口等。本文對部分特性進行解讀。

分區表性能

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

在阿里雲創建兩個同等規格（4c8g）的 RDS PostgreSQL 11 和 12 的實例，測試不同分區數情況下，使用 COPY 導入 1 億行數據的性能對比如下。可見，隨着分區數增多，導入性能始終優於 PostgreSQL 11。COPY 導入數據的性能提升得益於在 12 中支持了分區表批量插入，在次之前，僅支持一次一行的插入模式。

對於查詢操作，在 PostgreSQL 10 中，會依次檢查每個分區表，判斷其可能有滿足條件的數據，每個分區表的處理與普通表的處理流程類似；PostgreSQL 11 引入了分區裁剪特性，可以更早地定位需要訪問的分區；PostgreSQL 12 則近一步將分區裁剪功能前置，避免爲每個分區加載元數據並生成相應的內部結構，使得查詢計劃耗時進一步與無關的分區解耦。由此可見，該優化與查詢條件的分區過濾性相關，分區過濾性越好，所需處理的分區越少，優化效果越好。

不同分區數下，分區鍵（同時也是主鍵）上的查詢性能對比如下。可見，分區數越多，PostgreSQL 12 的性能提升越明顯，最高提升達 150 倍。而隨着分區數增加，PostgreSQL 12 的性能則保持相對穩定。

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

索引增強

B-tree 索引被廣泛應用於數據庫系統中，可以有效減少查詢需要訪問的數據量，提升查詢性能。索引是一種 "空間換時間" 的查詢優化策略，本身也會佔用一些存儲空間，其性能對查詢也至關重要。PostgreSQL 12 提升了標準 B-tree 的整體性能，減少了磁盤空間佔用，對於複合索引，其空間使用率最多可減少 40%，可以有效節省用戶的磁盤空間；對於有重複項的 B-tree 索引，其性能也有所提升。另外，引入 REINDEX CONCURRENTLY 命令，用戶可以在業務無感知的情況下重建索引。

我們通過測試直觀感受一下 B-tree 索引的空間佔用優化。分別在 PostgreSQL 11 和 12 中創建如下表和索引，並插入 2000 萬行數據，VACUUM 更新統計信息。

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)

2 .# 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%，在索引較多的場景下，如此大幅度的空間節省還是很可觀的。

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

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

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

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

參數控制 Prepared 計劃

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

執行** 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)

1.# 後續四次執行的結果在此省略

執行第 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)

重新執行 > PREPARE

，並設置 > plan_cache_mode

爲 > force_generic_plan

，觀察計劃使用情況，可見第 1 次執行時就會使用通用計劃，而無需等到第 6 次執行。

postgres=> DEALLOCATE p;

DEALLOCATE

postgres=> prepare p(integer) as select aid from foo where aid=$1;

PREPARE

(1) # 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)

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

可插拔表存儲接口

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

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

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

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)

豐富的插件支持

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

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

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

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

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

• zhparser 中文分詞插件，助力實現中文的全文檢索。

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

總結

RDS PostgreSQL 12 無論功能和性能都有很大提升，包括分區表查詢性能優化，B-tree 索引空間優化和性能提升，參數方式選擇 Prepare 語句執行計劃，內置的、功能全面的 SQL/JSON 路徑語言和更加豐富的插件支持。可插拔表訪問接口作爲未來支持多存儲引擎的基礎，意義重大，目前仍然只支持 Heap 表，用戶測暫時不會有感知。 除本文介紹的特性外，該版本還有很多其他特性，如多列 MCV（Most-Common-Value）統計，內聯 CTE（Common table expressions）等，文中未及介紹，感興趣的讀者可以參考相關文獻或者在阿里雲購買實例進行體驗

相關文章