PostgreSQL 擴充套件（extension）是一組可以加進 PostgreSQL 系統目錄（catalog）的 SQL 物件。安裝與啟用擴充套件可以在執行期完成——部署一個擴充套件，簡單到只要打一條 SQL 指令。

擴充套件涵蓋的需求非常多樣，粗略可分為幾類：

  • 給應用程式開發者的擴充：為 SQL 查詢帶來新的專門能力。例如 PostGIS——空間資料庫擴充，讓你能在 SQL 裡執行地理位置查詢。
  • 給 PostgreSQL 維運人員（ops、DBA）的擴充：提供新的內部檢視或管理工具。例如 pageinspect，可低階檢視資料庫分頁內容，便於除錯。
  • 可插拔語言（pluggable language）擴充：讓你能用某種程式語言撰寫預存程序與函式。PostgreSQL 核心維護的程序語言包括 PL/C、PL/SQL（其實只是把純 SQL 包進函式定義）、PL/pgSQL（把 SQL 當一級公民並提供程序式控制結構）、PL/TCL、PL/Perl、PL/Python；核心以外還有 PLV8（伺服器端 JavaScript）、PL/Java、PL/Lua 等外部專案。
  • 外部資料包裝器（Foreign Data Wrapper, FDW）擴充：實作 SQL 標準中的 SQL/MED（Management of External Data）設計，存取 PostgreSQL 之外管理的資料。內建的有讀檔案的 file_fdw 與連遠端 PostgreSQL 的 postgres_fdw；社群另有 oracle_fdw、ldap_fdw 等，清單又長又雜，可查 PostgreSQL wiki 的 foreign data wrappers 頁面。

擴充套件的內部構造#

任何 SQL 物件都可以是擴充套件的一部分。常見的物件包括：

  • 預存程序（stored procedure）
  • 資料型別（data type）
  • 運算子（operator）、運算子類別（operator class）、運算子家族（operator family）
  • 索引存取方法（index access method）

以 contrib 的 pg_trgm 為例，安裝後即可觀察它包含什麼：

create extension pg_trgm;

接著用 psql 的 \dx+ pg_trgm 列出其中的物件。

延伸輸出：pg_trgm 內含的 36 個物件（節錄）
                   Objects in extension "pg_trgm"
                         Object description
═══════════════════════════════════════════════════════════════════
 function gin_extract_query_trgm(text,internal,smallint,...)
 function gin_extract_value_trgm(text,internal)
 function gtrgm_in(cstring)
 function gtrgm_out(gtrgm)
 function set_limit(real)
 function show_limit()
 function show_trgm(text)
 function similarity(text,text)
 function word_similarity(text,text)
 ...
 operator %(text,text)
 operator %>(text,text)
 operator <%(text,text)
 operator <->(text,text)
 operator <->>(text,text)
 operator <<->(text,text)
 operator class gin_trgm_ops for access method gin
 operator class gist_trgm_ops for access method gist
 operator family gin_trgm_ops for access method gin
 operator family gist_trgm_ops for access method gist
 type gtrgm
(36 rows)

從輸出可以看出：

  • 列出的 function 都是預存程序，這個擴充套件裡剛好都以 C 撰寫。
  • 新的運算子如 % 實作相似度比對測試（後續章節詳述）。
  • operator class 與 operator family 是「黏著」物件：它們把涵蓋這些運算子的索引存取方法登錄進系統目錄，讓查詢規劃器（planner）能夠決定使用新的索引。
  • 擴充套件還實作了一個以 C 撰寫的新資料型別，同樣在執行期安裝——不必重新編譯 PostgreSQL，本例中甚至不必重啟伺服器。

安裝與使用擴充套件#

擴充套件是安裝在某個資料庫裡的，即使它的部署包含通常屬於系統層級的共享函式庫（依作業系統可能是 .so、.dll 或 .dylib）。

支援檔案部署到作業系統的正確位置後，只要一條 SQL 就能在目前連線的資料庫啟用：

create extension pg_trgm;

支援檔案本身則透過作業系統的套件管理安裝。以 Debian 為例（建議使用 http://apt.postgresql.org ↗ 的 PostgreSQL Debian 發行版），要讓 PostgreSQL 10 可安裝 pg_trgm，安裝對應的 contrib 套件即可：

$ sudo apt-get install postgresql-contrib-10

要確認哪些擴充套件已可供你的 PostgreSQL 實例使用：

table pg_available_extensions;
      name       │ default_version │ installed_version │            comment
═════════════════╪═════════════════╪═══════════════════╪═══════════════════════════════
 pg_prewarm      │ 1.1             │ ¤                 │ prewarm relation data
 pgcrypto        │ 1.3             │ ¤                 │ cryptographic functions
 plpgsql         │ 1.0             │ 1.0               │ PL/pgSQL procedural language
 pg_buffercache  │ 1.3             │ ¤                 │ examine the shared buffer cach
 ...
(10 rows)

尋找擴充套件#

  • 第一批值得認識的擴充套件就是 contrib 本身。務必在每個使用 PostgreSQL 的環境都部署 contrib 的作業系統套件——其中有些擴充專門用來診斷棘手狀況（例如檢查資料表或索引是否損毀），需要時一條 create extension 就能取得診斷工具。
  • 另一個來源是 PostgreSQL Extension Network（PGXN），擴充作者可自行登錄專案並隨版本更新資訊。

無論 contrib 或 PGXN，都不保證清單上擴充套件的品質，必須自行測試。本書涵蓋的都是已知達到生產品質、可以信賴的擴充，並附上一份值得信任的清單；但清單並不窮盡——找到未列出的擴充，也絕對值得一試。

撰寫擴充套件入門#

PostgreSQL 讓撰寫擴充套件變得容易。多數擴充為了存取低階設施而以 C 撰寫，但並非絕對——也可以用 PL/Perl、PL/Python 甚至 PL/pgSQL 等高階語言。若你的應用已在預存程序中維護部分邏輯，擴充套件機制會很有用；官方文件的〈Extension Building Infrastructure〉一節詳述了步驟。

需要準備的檔案：

  • Makefile：需要「建置」時才要，主要是 C 擴充的情況
  • control 檔：描述擴充套件的屬性
  • SQL 安裝腳本：建立擴充物件（資料表、視圖、函式、預存程序、運算子、資料型別等）
  • SQL 升級腳本：從一個版本升到下一個版本

擴充套件功能在 PostgreSQL 9.1 加入的理由只有一個：讓使用外部模組的資料庫也能無縫地 pg_dump 與 pg_restore。作者（Dimitri Fontaine）正是這個功能的實作者，該補丁由他提交進 PostgreSQL。

值得注意的擴充套件短清單#

在資料庫伺服器裡擁有更多資料處理工具是好事：面對複雜問題時，可以得到交易觀點正確且資料流觀點高效的解法。以下先列 contrib 中給應用開發者的擴充：

  • bloom：基於布隆過濾器（bloom filter）的索引存取方法。布隆過濾器是節省空間的資料結構，用來測試元素是否屬於集合；索引以建立時固定大小的簽章（signature）快速排除不符合的 tuple。簽章是有損表示，可能誤報（false positive），所以索引搜尋結果必須用堆積（heap）中的實際值重新檢查。這種索引最適合「資料表有很多屬性、查詢會測試任意組合」的情境——一個 bloom 索引能取代許多 btree 索引，但只支援等值查詢。

  • earthdistance：提供兩種計算地球表面大圓距離（great circle distance）的方式，一種依賴 cube 模組，另一種基於內建 point 型別以經緯度表示。此模組假設地球是完美球體；若精度不夠，考慮 PostGIS。

  • hstore：在單一 PostgreSQL 值中儲存鍵值對集合的資料型別，適合「屬性很多但很少被查看的資料列」或半結構化資料。鍵與值都是純文字字串。

  • ltree：表示階層樹狀結構標籤的資料型別，並提供豐富的搜尋功能，例如：

    SELECT path FROM test WHERE path @ 'Astro* & !pictures@';

  • pg_trgm：基於 trigram 比對的文字相似度函式與運算子，並提供支援快速相似字串搜尋的索引運算子類別。

接下來是獨立於主專案維護的擴充——它們有自己的團隊、組織與發行週期：

  • PostGIS：空間資料庫擴充，加入地理物件支援，讓位置查詢直接在 SQL 中執行，功能遠超 Oracle Locator/Spatial 與 SQL Server 等競品：

    SELECT superhero.name
  FROM city, superhero
 WHERE ST_Contains(city.geom, superhero.geom)
   AND city.name = 'Gotham';

  • ip4r：IPv4/v6 及其範圍的索引型別。內建 inet/cidr 型別對「哪個 IP 範圍包含這個位址」（column >>= parameter）的索引查找支援不佳，且 inet 把 netblock 與特定 IP 兩種概念混在一起、為支援 IPv6 而成為負擔較重的變長型別；ip4r 提供更輕量的表示。

  • citus：以分片（sharding）與複寫把 PostgreSQL 水平擴展到多台通用伺服器，查詢引擎將 SQL 平行分散執行，在大型資料集上取得即時回應。

  • pg_partman：建立與管理時間型及序號型分割資料表集合的擴充；自 v3.0.1 起支援 PostgreSQL 10 原生分割。子表建立全由擴充自行管理。

  • postgresql-hll：引入 hll 資料型別——HyperLogLog 是固定大小、類似集合的結構，用於可調精度的不重複值計數；1280 位元組即可估算數百億個不重複值，誤差僅幾個百分點。

  • prefix：前綴比對在電話應用中極常見（話務路由與計費取決於電話號碼與電信商前綴的比對）。典型查詢是找最長前綴：

      SELECT *
    FROM prefixes
   WHERE prefix @> '0123456789'
ORDER BY length(prefix) DESC
   LIMIT 1;

  • MADlib：Apache MADlib 是開源的可擴展資料庫內分析函式庫，提供數學、統計與機器學習方法的資料平行實作。

  • RUM：基於 GIN 程式碼的新索引存取方法，透過在 posting tree 中儲存額外資訊（如詞位位置或時間戳記），解決 GIN 在排名、片語搜尋與依時間排序上的效能問題。用 PostgreSQL 做全文檢索的話值得一看。

從這份清單就能看出 PostgreSQL 可擴充性的威力：有的擴充提供新資料型別、運算子外加索引支援；有的甚至實作自己的 SQL 規劃器與最佳化器（如 Citus，藉此把查詢執行路由到分散式的 PostgreSQL 實例網路）。而所有擴充都能倚賴 PostgreSQL 的工業級強項——交易語意帶來的正確性、耐久性與崩潰安全、先進規劃器與成本式最佳化器帶來的效能，以及開源的專案與協定。

Backward PostgreSQL 擴充套件 用 hstore 稽核異動 Forward