可插拔儲存引擎#

PostgreSQL 所使用的資料佈局既非唯一可能，也不是對所有負載類型都最好的。依循可擴充性的理念，PostgreSQL 允許你建立並插入各種表格存取方法（可插拔儲存引擎），但目前開箱即用的只有一種：

=> SELECT amname, amhandler FROM pg_am WHERE amtype = 't';
 amname |      amhandler
−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−−
 heap   | heap_tableam_handler
(1 row)

建立表格時可用 CREATE TABLE ... USING 指定引擎；否則套用 default_table_access_method（預設 heap）所列的預設引擎。

核心與引擎的分工#

為了讓 PostgreSQL 核心以相同方式處理各種引擎，表格存取方法必須實作一個特殊介面。amhandler 欄位所指的函式回傳含核心所需全部資訊的介面結構。

所有表格存取方法都能使用的核心元件：

• 交易管理器，含 CLOG 與快照隔離支援
• 緩衝管理器
• I/O 子系統
• TOAST
• 最佳化器與執行器
• 索引支援

引擎自己定義的部分：

• 元組格式與資料結構
• 表格掃描的實作與成本估算
• 插入、刪除、更新與鎖定操作的實作
• 可見性規則
• 清理與分析程序

就歷史而言，PostgreSQL 使用的是單一內建資料儲存、沒有適當的程式介面。因此現在很難設計出一個既涵蓋標準引擎所有特性、又不妨礙其他方法的好架構。

例如 WAL 該怎麼處理仍然不清楚：新的存取方法可能需要記錄核心一無所知的自有操作。既有的通用 WAL 機制通常是糟糕的選擇，因為開銷太大。你可以再加一個介面來處理新類型的 WAL 條目，但那樣一來當機復原就會依賴外部程式碼，這極不受歡迎。目前唯一看似可行的方案，是為每個特定引擎修補核心。

正因如此，本書並未嚴格區分表格存取方法與核心。前幾篇描述的許多特性，形式上屬於 heap 存取方法而非核心本身。

heap 方法很可能永遠是 PostgreSQL 的終極標準引擎，而其他方法將填補各自的利基，處理特定負載類型的挑戰。

循序掃描#

儲存引擎定義表格資料的實體佈局並提供存取方法。唯一支援的方法是循序掃描（sequential scan），它完整讀取表格主分支的檔案。每讀一頁就檢查每個元組的可見性，不滿足查詢的元組被濾掉。

掃描過程會經過緩衝快取；為確保大表格不會擠掉有用資料，會採用小尺寸的緩衝環。掃描同一表格的其他行程會加入這個緩衝環，從而避免額外的磁碟讀取——這類掃描稱為同步掃描（synchronized scan）。

因此，掃描未必總是從檔案開頭開始。

循序掃描是讀取整張表格或其大部分最有效率的方式。換言之，選擇性低時循序掃描價值最大；選擇性高（查詢只需選出少數列）時，使用索引較佳。

成本估算#

執行計畫中，循序掃描以 Seq Scan 節點表示：

=> EXPLAIN SELECT * FROM flights;
 Seq Scan on flights (cost=0.00..4772.67 rows=214867 width=63)

估算列數來自基礎統計（reltuples）。估算成本時，最佳化器考量兩個分量：

1. I/O 成本 = 表格頁數 × 單頁讀取成本（假設頁面被循序讀取）

緩衝管理器請求某頁時，作業系統實際上會從磁碟讀取更多資料，因此後續數頁極可能已在 OS 快取中——這正是循序讀取的單頁成本低於隨機存取成本的原因。

預設設定適用於 HDD；若使用 SSD，大幅調低 random_page_cost 是合理的（seq_page_cost 通常維持原樣，作為參考基準）。由於這兩個參數的最佳比值取決於硬體，它們通常在表空間層級設定（ALTER TABLESPACE ... SET）。

這些計算清楚顯示了清理不及時所導致的表格膨脹的後果：表格主分支越大，必須掃描的頁面就越多，不論其中含有多少活元組。

2. CPU 資源成本 = 元組數 × cpu_tuple_cost（預設 0.01）

兩者之和即為計畫的總成本。啟動成本為零，因為循序掃描沒有前置條件。

若被掃描的表格需要過濾，套用的過濾條件會出現在 Seq Scan 節點的 Filter 區段中。EXPLAIN ANALYZE 會同時顯示實際回傳列數與被濾掉的列數：

=> EXPLAIN (analyze, timing off, summary off)
SELECT * FROM flights WHERE status = 'Scheduled';
 Seq Scan on flights
   (cost=0.00..5309.84 rows=15383 width=63)
   (actual rows=15383 loops=1)
   Filter: ((status)::text = 'Scheduled'::text)
   Rows Removed by Filter: 199484

=> EXPLAIN SELECT count(*) FROM seats;
 Aggregate (cost=24.74..24.75 rows=1 width=8)
   −> Seq Scan on seats (cost=0.00..21.39 rows=1339 width=0)

 reltuples | cpu_operator_cost | cpu_cost
−−−−−−−−−−−+−−−−−−−−−−−−−−−−−−−+−−−−−−−−−−
      1339 | 0.0025            |     3.35

平行計畫#

PostgreSQL 支援平行查詢執行：執行查詢的領導行程（leader）透過 postmaster 衍生數個工作行程（worker），它們同時執行計畫中相同的平行部分。結果傳回領導行程，由它在 Gather 節點中彙整。

不接收資料時，領導行程也可能參與計畫平行部分的執行。若需要，可關閉 parallel_leader_participation 參數（預設 on）禁止領導行程的貢獻。

啟動這些行程與在它們之間傳送資料並非免費，因此遠非所有查詢都該被平行化。

此外，即使允許平行執行，也不是計畫的所有部分都能並行處理——某些操作由領導行程獨自以循序模式執行。

PostgreSQL 不支援另一種平行執行方式：讓數個工作者形成一條生產線來處理資料（粗略地說，每個計畫節點由一個獨立行程執行）。PostgreSQL 開發者認為這個機制沒有效率。

平行循序掃描#

專為平行處理設計的節點之一是 Parallel Seq Scan。

這個名字聽起來有點矛盾（掃描究竟是循序還是平行？），但它反映了操作的本質：就檔案存取而言，表格頁面是循序讀取的，順序與單純的循序掃描相同；然而這項操作由數個並行行程執行。為避免同一頁被掃描兩次，執行器透過共享記憶體同步這些行程。

微妙之處在於：作業系統看不到「循序掃描」的全貌，它看到的是數個執行隨機讀取的行程。因此通常能加速循序掃描的資料預取幾乎失效。

為降低這個不愉快的效應，PostgreSQL 指派給每個行程的不只一頁，而是數個連續頁面。

就其本身而言，平行掃描意義不大——一般的讀取成本還被行程間資料傳輸的開銷進一步推高。

然而，若工作者對取得的列進行任何後處理（例如聚合），總執行時間可能大幅縮短。

成本估算#

=> EXPLAIN SELECT count(*) FROM bookings;
 Finalize Aggregate (cost=25442.58..25442.59 rows=1 width=8)
   −> Gather (cost=25442.36..25442.57 rows=2 width=8)
       Workers Planned: 2
       −> Partial Aggregate
           (cost=24442.36..24442.37 rows=1 width=8)
           −> Parallel Seq Scan on bookings
               (cost=0.00..22243.29 rows=879629 width=0)

Parallel Seq Scan 的 rows 顯示的是單一行程預計處理的平均列數。

總共由三個行程執行（一個領導、兩個工作者），但領導行程處理的列較少：工作者越多，它的份額越小。本例中除數為 2.4（2111110 / 2.4 = 879629）。

成本計算與循序掃描類似，但：

• CPU 部分較小，因為每個行程處理的列較少
• I/O 部分完整計入，因為整張表仍必須逐頁讀完

relpages × seq_page_cost + (reltuples / 2.4) × cpu_tuple_cost = 22243.29

接著 Partial Aggregate 對取得的資料做聚合，成本以一般方式估算並加到表格掃描的成本上。

Gather 節點由領導行程執行，負責啟動工作者並蒐集它們回傳的資料：

本例中啟動成本佔主導（1000），加到 Partial Aggregate 的啟動成本上；總成本另加兩列的傳輸成本。

最後 Finalize Aggregate 彙整 Gather 從各平行行程收到的部分結果。

• 聚合在取得所有輸入列之前無法回傳結果，因此其啟動成本基於下層節點的總成本
• Gather 相反，資料一取得就開始往上送，因此其啟動成本取決於下層節點的啟動成本，總成本則基於下層節點的總成本

平行執行的限制#

背景工作者的數量#

行程數量由三個層級的參數控制：

平行查詢執行並非唯一需要背景工作者的操作——邏輯複寫也參與其中，擴充也可能使用它們。

這些參數值的選擇取決於：

• 硬體能力：系統必須有專供平行執行的空閒核心
• 表格大小：資料庫必須含有大表格
• 典型負載：必須存在可能受惠於平行執行的查詢

工作者數量的計算公式#

若估算的待讀 heap 資料量不超過 min_parallel_table_scan_size（預設 8 MB），規劃器根本不會考慮平行執行。

除非用 parallel_workers 儲存參數為特定表格明確指定，否則行程數依下式計算：

1 + ⌊ log₃(表格大小 / min_parallel_table_scan_size) ⌋

意思是：表格每長大三倍，PostgreSQL 就多指派一個平行工作者。預設設定下：

無論如何，平行工作者數不能超過 max_parallel_workers_per_gather 所定義的上限。

此外，若查詢執行期間空閒的名額少於規劃值，就只會啟動可用數量的工作者（計畫中可見 Workers Planned 與 Workers Launched 不一致）。

不可平行化的查詢#

並非所有查詢都能被平行化。以下類型無法使用平行計畫：

• 修改或鎖定資料的查詢（INSERT、UPDATE、DELETE、SELECT FOR UPDATE 等）
• 可被暫停的查詢：適用於游標內執行的查詢，包括 PL/pgSQL 中的 FOR 迴圈
• 呼叫 PARALLEL UNSAFE 函式的查詢。預設所有使用者定義函式與少數標準函式屬此類，可查 SELECT * FROM pg_proc WHERE proparallel = 'u'
• 函式內的查詢，若該函式是從已平行化的查詢中呼叫（避免工作者數量遞迴增長）

資料修改的限制不適用於下列指令中的子查詢（PostgreSQL 11／14 起）：CREATE TABLE AS、SELECT INTO、CREATE MATERIALIZED VIEW、REFRESH MATERIALIZED VIEW。不過這些情況下列的插入仍是循序執行的。

部分限制可能在未來版本被移除。例如在 Serializable 隔離層級平行化查詢的能力已經具備（PostgreSQL 12 起）。

查詢未被平行化可能有幾個原因：

1. 這類查詢根本不支援平行化
2. 伺服器組態禁止使用平行計畫（例如受表格大小限制）
3. 平行計畫比循序計畫更貴

要檢查某查詢是否根本上能被平行化，可暫時開啟 force_parallel_mode 參數，規劃器就會在可能時建立平行計畫。

受限於平行的操作#

計畫的平行部分越大，潛在的效能提升越多。然而某些操作嚴格由領導行程循序執行，即使它們本身不妨礙平行化——換言之，它們不能出現在計畫樹中 Gather 節點之下。

1. 不可展開的子查詢

最明顯的例子是掃描 CTE 的結果（計畫中的 CTE Scan 節點）：

=> EXPLAIN (costs off)
WITH t AS MATERIALIZED (SELECT * FROM flights)
SELECT count(*) FROM t;
 Aggregate
   CTE t
     −> Seq Scan on flights
   −> CTE Scan on t

若 CTE 未被物化（PostgreSQL 12 起的預設行為），計畫中就不含 CTE Scan 節點，此限制便不適用。

另外要注意：CTE 本身若被判定較便宜，仍可以平行模式計算。

其他例子：

• SubPlan 節點：其父節點無法參與平行執行，因為它依賴 SubPlan 回傳的資料。SubPlan 會被執行多次（每一列一次）。
• InitPlan 節點：與 SubPlan 不同，InitPlan 只被求值一次。InitPlan 的父節點無法參與平行執行，但接收 InitPlan 求值結果的節點可以。

2. 暫存表格

暫存表格不支援平行掃描，因為它們只能被建立它們的行程存取，其頁面在本地緩衝快取中處理。

讓本地快取可供多個行程存取，會需要像共享快取那樣的鎖定機制——那將使本地快取的其他優點大打折扣。

3. 受限於平行的函式

定義為 PARALLEL RESTRICTED 的函式只允許出現在計畫的循序部分。可查 SELECT * FROM pg_proc WHERE proparallel = 'r' 取得清單。