聚焦六大功能：PostgreSQL 18 新特性深度解析詳情 - 數據庫,sql IvorySQL 動態日志

PostgreSQL 全球開發組於 2025 年 5 月 8 日發佈了 PostgreSQL 18 的首個 Beta 版本，正式版也已於 9 月 25 日正式上線。本文 IvorySQL 社區將為大家拆解 PostgreSQL 18 的六大亮點特性。

一、PG 異步 I/O（AIO）框架：邁出打破同步阻塞瓶頸的第一步

PostgreSQL 18 全新引入異步 I/O 子系統。新機制允許特定場景下並行執行多個異步預讀操作，CPU 無需等待數據返回即可繼續推進查詢，一定程度上降低了等待損耗。此框架為 PG 未來更深入更徹底的異步 I/O 性能優化奠定基礎，邁出了第一步。

核心提升場景

【當前僅實現異步讀，沒有實現異步寫】 所有 Seq Scan 場景下通過適配過異步 I/O 的 ReadStream 設施可實現並行化順序預讀，提升 Seq Scan 的性能，效果好於原有 posix_fadvice 的建議性預讀。尤其在雲存儲場景下單次阻塞讀 I/O 相比本地 I/O 所需時間更長，異步 I/O 加持下的並行化預讀的優勢更加明顯。目前異步 I/O 已支持順序掃描、位圖堆掃描和 VACUUM 操作的異步讀取，早期測試顯示，讀取密集型查詢性能可提升 2-3 倍。

如圖所示

aio.drawio.png

使用了異步 I/O 的 ReadStream 機制可以在收到讀請求後異步地預讀後續可能使用的 buffer。而在使用同步 I/O 方式在每次請求讀取 buffer 時，都要等待 I/O 操作完成，這樣降低了系統吞吐量。

使用方法

# - I/O -

#backend_flush_after = 0        # measured in pages, 0 disables
effective_io_concurrency = 300        # 1-1000; 0 disables issuing multiple simultaneous IO requests
maintenance_io_concurrency = 300    # 1-1000; same as effective_io_concurrency
io_max_combine_limit = 256kB        # usually 1-128 blocks (depends on OS)
                    # (change requires restart)
io_combine_limit = 256kB        # usually 1-128 blocks (depends on OS)

io_method = io_uring # worker, io_uring, sync
                    # (change requires restart)
io_max_concurrency = 128        # Max number of IOs that one process
                    # can execute simultaneously
                    # -1 sets based on shared_buffers
                    # (change requires restart)
#io_workers = 3                # 1-32;

用户可選擇三種不同的 io_method 啓用異步 I/O，分別是：

worker 若干後台 I/O workers 接收處理後端進程的 I/O 請求。
io_uring Linux 系統中 io_uring 子系統通過操作系統內核線程處理 PG 的 I/O 請求。
sync 滿足異步 I/O 框架接口要求的同步 I/O。

要啓用異步 I/O，用户需要根據自身情況設定上述 GUC 參數。其中每個進程能夠擁有的最大異步 I/O 句柄為 io_max_concurrency，用户可以將其置 -1，使數據庫自行選擇合適的值。若自行選擇的值太大，則可能因為異步 I/O 所佔空間太大而無法啓動數據庫；若自行選擇的值太小則無法完全發揮出異步 I/O 性能。

啓動數據庫後，用户可通過 pg_aios 視圖實時地獲取當前系統異步 I/O 執行狀況:

postgres=# select * from pg_aios;
-[ RECORD 1 ]---+-------------------------------------------
pid             | 85834
io_id           | 14208
io_generation   | 204
state           | SUBMITTED
operation       | readv
off             | 116252672
length          | 8192
target          | smgr
handle_data_len | 1
raw_result      |
result          | UNKNOWN
target_desc     | block 14191 in file "base/5/16427"
f_sync          | f
f_localmem      | f
f_buffered      | t
...

各列含義詳見官方文檔。

框架設計

PG 18 引入異步 I/O 框架，支持通過 GUC 參數靈活配置異步 I/O：包括實現方式（io_method，可選 worker、io_uring 或 sync）、併發規模（如 *_io_concurrency、io_max_concurrency）及實現相關參數（如 io_workers）。

該框架對 I/O 目標（當前支持 smgr，未來計劃支持 WAL）和不同階段、不同數據源(shared buffer/local buffer)的行為進行了抽象（通過 PgAioHandleCallbacks 結構），以支持後續擴展。相關內存於啓動時在共享內存中分配，後續不再縮放。進程按編號訪問所屬異步 I/O 資源，句柄通過 generation 號標記複用。

目前該版本異步 I/O 主要提供對 smgr 的異步讀支持，尚不支持 WAL 異步讀寫，smgr 的異步寫入功能仍在開發中。

對原有設施的修改

擴展 smgr 接口：新增 smgr_startreadv 方法以支持異步讀取。
實現回調結構：smgr 需實現 PgAioTargetInfo 和 PgAioHandleCallBacks 回調結構。
適配現有模塊：smgr 和 buffer manager 等模塊需填充異步 I/O 抽象結構以兼容框架。
PG 臨界區處理：同步 I/O 可在 PG 臨界區內發起；異步 I/O 因分段執行且帶回調，需移除回調中可能失敗的操作（臨界區內一切操作不能失敗，如用RelPathStr替代 palloc 的char*字符串）以確保安全。
優化上層接口：利用異步 I/O 改造 ReadStream 等接口，實現真預讀，大幅提升順序掃描、pg_prewarm 及 ANALYZE 等操作的 I/O 性能，效果優於原有 posix_fadvise 方案。

使用注意及未來展望

io_uring 需要較新內核：舊版 Linux Kernel 不支持 io_uring。某些早期版本內核雖然支持 io_uring，但功能和性能表現與新內核有一定差距。
架構限制併發粒度：受多進程架構所限，PG 異步 I/O 期間可並行運行的計算任務較少，難以實現更細粒度的任務級異步。當前主要性能收益集中於ReadStream 順序預讀及等並行 I/O 操作。
未來可能的性能提升：Linux io_uring 支持直接 I/O（DIO）特性，為在 PG 中啓用 DIO 奠定基礎。未來啓用直接 I/O 可免除雙重 buffer(OS 層面對 I/O 數據進行 buffer，PG 層面對 I/O 進行 buffer)以減少不必要的數據複製。在高速 NVMe 上還配合 DIO 啓用IORING_SETUP_IOPOLL選項使用輪訓方式檢查 I/O 完成情況，還可以進一步提升性能。
未來更多的異步 I/O 後端：除了sync模式，正式版的 PostgreSQL 18 僅支持 worker 和 io_uring 兩種異步 I/O 後端。目前異步 I/O 框架設計已基本完備，未來版本有望支持 Windows IORing，IOCP，以及 Posix 異步 I/O 等 I/O 後端，為用户提供更多選擇。

小結

PostgreSQL 18 異步 I/O 框架提升數據庫系統 I/O 能力，同時也增強了 PostgreSQL 架構的可擴展性，用户只需要根據自身情況修改 GUC 參數即可獲取到異步 I/O 帶來的好處。目前異步 I/O 框架設計基本完備，後期支持其他異步 I/O 後端也將非常方便。任意平台 Postgres 用户可以嘗試 io_method = worker或者sync 若要在非官方適配 io_uring 的舊 Linux 內核發行版上使用 io_uring 後端，需要進行充分測試後再使用。

二、跳躍式掃描：讓 B 樹索引 “提速換擋”

在 PostgreSQL 18 之前的版本中，多列 B 樹索引可用於包含該索引中任意子集列的查詢條件，在對起始（最左側）列施加約束時最為高效。對前導列的等式約束，加上對第一個不帶有等式約束的列的任何不等式約束，用於限制要掃描的索引部分。

例如，給定一個基於（a，b，c）非空字段的升序索引和查詢條件 WHERE a = 5 AND b >= 42 AND c < 77，該索引將從具有 a = 5 和 b = 42 的第一個條目開始掃描，一直掃描到最後具有 a = 5 的條目。具有 c >= 77 的索引條目將被跳過，但它們仍需掃描。

原則上，這種索引可以用於對 b 和/或 c 有約束條件而對 a 沒有約束條件的查詢——但必須掃描整個索引，所以在大多數情況下優化器更傾向於對錶進行順序掃描表，而非利用索引掃描。

核心提升場景

從 PostgreSQL 18 開始：

如果 B 樹索引掃描能夠應用跳躍式掃描（SKIP SCAN），在遍歷索引時應用每個列的約束，可以減少索引的讀取。跳躍式掃描的工作原理是內部生成一個動態等式約束，該約束與索引列中的每個可能值相匹配。

效果測試

對比版本：

PostgreSQL 17 vs PostgreSQL 18

表結構與索引

CREATE TABLE t1
(
c1 int
c2 int,
c3 float
)
WITH (fillfactor=80);


CREATE INDEX idx_t1_c1c2 ON t1(c1, c2);

數據生成

INSERT INTO t1
SELECT (random()*1000)::int, (random()*10000)::int, random()
FROM generate_series(1,1000000) g;

通過 COPY 導入數據

COPY t1 FROM '/.../t1.csv' WITH (FORMAT csv);

查詢語句使用複合索引的第二個列

EXPLAIN ANALYZE SELECT * FROM t1 WHERE c2=100;

PostgreSQL 17 執行計劃選擇使用並行順序掃描

                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..12986.33 rows=100 width=16) (actual time=1.125..76.076 rows=90 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on t1  (cost=0.00..11976.33 rows=42 width=16) (actual time=1.414..68.624 rows=30 loops=3)
         Filter: (c2 = 100)
         Rows Removed by Filter: 333303
 Planning Time: 0.792 ms
 Execution Time: 76.165 ms
(8 rows)

關閉順序掃描強制選擇索引掃描，並非最優計劃，執行更慢。

                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_t1_c1c2 on t1  (cost=0.42..18773.42 rows=100 width=16) (actual time=1.846..100.758 rows=90 loops=1)
   Index Cond: (c2 = 100)
 Planning Time: 0.147 ms
 Execution Time: 100.806 ms
(4 rows)

PostgreSQL 18 執行計劃選擇使用索引掃描，可以看出跳躍式掃描執行效率提升幅度非常大

                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_t1_c1c2 on t1  (cost=0.42..3900.84 rows=100 width=16) (actual time=0.225..11.464 rows=90.00 loops=1)
   Index Cond: (c2 = 100)
   Index Searches: 1002
   Buffers: shared hit=3096
 Planning Time: 0.141 ms
 Execution Time: 11.522 ms
(6 rows)

關閉索引掃描和位圖掃描強制選擇順序掃描。

postgres=# EXPLAIN ANALYZE SELECT * FROM t1 WHERE c2=100;
                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..12986.33 rows=100 width=16) (actual time=1.486..86.881 rows=90.00 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   Buffers: shared hit=6768
   ->  Parallel Seq Scan on t1  (cost=0.00..11976.33 rows=42 width=16) (actual time=2.758..78.712 rows=30.00 loops=3)
         Filter: (c2 = 100)
         Rows Removed by Filter: 333303
         Buffers: shared hit=6768
 Planning Time: 0.141 ms
 Execution Time: 86.926 ms
(10 rows)

使用注意

跳躍式掃描目前只能支持等值比較條件。

小結

PostgreSQL 18 的索引跳躍式掃描，使得多列 BTREE 索引能夠被那些僅對第二個或之後的索引列進行等值引用的查詢使用，大幅減少索引掃描需要訪問的條目，使其效率得到明顯提升。

三、虛擬生成列：存儲與計算的 “靈活平衡”

PostgreSQL 18 開發體驗相關的特性，聚焦於簡化開發流程、提升代碼靈活性，讓開發者更高效地利用 PostgreSQL 能力。

IvorySQL 數據庫長期致力於 Oracle 特性兼容，其中包含了一項虛擬列的語法兼容：

column [datatype][generated always] AS (column_expression)[VIRTUAL]

這次 PostgreSQL 18 終於也帶來了虛擬列功能。虛擬列是一種不存儲數據的表列，其值在查詢時通過動態計算得出。與存儲列相比，虛擬列節省了列存儲空間，查詢虛擬列值時通過計算虛擬列表達式的值作為該列的值。

基本語法

PostgreSQL 18 中虛擬列的語法和存儲列的語法相似，新增加關鍵字VIRTUAL，當省略 STORED 和 VIRTUAL 關鍵字時默認為虛擬列。其語法如下所示：

GENERATED ALWAYS AS ( generation_expr ) [ STORED | VIRTUAL ]

虛擬列用例

虛擬列的標識是在列的限制條件中表示的，通過虛擬列的限制語法標識列為虛擬列，以下為虛擬列表的創建、查詢和新增虛擬列：

-- 創建包含虛擬列的表，其中price_with_tax為虛擬列
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC(10,2) NOT NULL,
    tax_rate NUMERIC(5,2) DEFAULT 0.20,
    price_with_tax NUMERIC(10,2) GENERATED ALWAYS AS (price * (1 + tax_rate)) VIRTUAL
);

-- 插入數據
INSERT INTO products (name, price, tax_rate)
VALUES ('Laptop', 1000.00, 0.20);

-- 查詢數據（虛擬列自動計算）
SELECT name, price, tax_rate, price_with_tax FROM products;
  name  |  price  | tax_rate | price_with_tax
--------+---------+----------+----------------
 Laptop | 1000.00 |     0.20 |        1200.00
(1 row)

--為表添加虛擬列
ALTER TABLE products ADD COLUMN selling_price NUMERIC(10,2)
GENERATED ALWAYS AS (
    price * (1 - 0.2) * (1 + tax_rate)
) virtual;

實現原理淺析

虛擬列的創建

創建的表中虛擬列的存儲方式和普通列的存儲方式類似，其列信息都存儲在 pg_attribute 系統表中，其中 attgenerated 列存儲生成列信息，如果該列的值為's'，表示該列為存儲列。PostgreSQL 18 新增的虛擬列在該字段中的標識符為'v'，並且將虛擬列的表達式存儲於 pg_attrdef 系統表中。

--查看虛擬列信息，其attgenerated為v表示該列為虛擬列
postgres=# select * from pg_attribute where attname='price_with_tax';
-[ RECORD 1 ]--+---------------
attrelid       | 16388
attname        | price_with_tax
atttypid       | 1700
attlen         | -1
attnum         | 5
atttypmod      | 655366
attndims       | 0
attbyval       | f
attalign       | i
attstorage     | m
attcompression |
attnotnull     | f
atthasdef      | t
atthasmissing  | f
attidentity    |
attgenerated   | v
attisdropped   | f
attislocal     | t
attinhcount    | 0
attcollation   | 0
attstattarget  |
attacl         |
attoptions     |
attfdwoptions  |
attmissingval  |

--查看虛擬列表達式存儲，可以看到以下表達式為虛擬列表達式
postgres=# select pg_get_expr(adbin, adrelid) from pg_attrdef where adnum = 5;
             pg_get_expr
-------------------------------------
 (price * ((1)::numeric + tax_rate))
(1 row)

虛擬列的插入或更新

由於虛擬列的數據不佔據存儲空間，所以任何指定更新或插入虛擬列的操作都將被限制。

--指定插入虛擬列
INSERT INTO products (name, price, tax_rate, price_with_tax) VALUES ('Laptop', 1000.00, 0.20, 1);
ERROR:  cannot insert a non-DEFAULT value into column "price_with_tax"
DETAIL:  Column "price_with_tax" is a generated column.

--指定更新虛擬列
update products set price_with_tax = 1 where name = 'Laptop';
ERROR:  column "price_with_tax" can only be updated to DEFAULT
DETAIL:  Column "price_with_tax" is a generated column.

虛擬列的查詢

PostgreSQL 18 中查詢虛擬列的實現是在生成執行計劃階段完成。在邏輯重寫優化階段，判斷查詢的範圍表中是否包含虛擬列，如果包含虛擬列，則將該虛擬列的表達式從 pg_attrdef 中獲取出來並替換原虛擬列名。這樣查詢虛擬列的值就相當於計算其表達式的值，即 select price_with_tax 相當於 select (price \* ('1'::numeric + tax_rate)) as price_with_tax。可以看到以下虛擬列被替換成了其表達式：

postgres=# explain verbose SELECT name, price, tax_rate, price_with_tax
FROM products;
                              QUERY PLAN
----------------------------------------------------------------------
 Seq Scan on public.products  (cost=0.00..23.12 rows=750 width=76)
   Output: name, price, tax_rate, (price * ('1'::numeric + tax_rate))
(2 rows)

適用場景

當考慮存儲空間時，可以使用虛擬列，因為虛擬列不佔用磁盤空間。
當列的值需要根據依賴的列變化而變化時，需要使用虛擬列。因為虛擬列的值是動態獲取的。
當虛擬列表達式簡單時，可以使用虛擬列。因為查詢虛擬列需消耗 CPU 資源，表達式複雜會消耗太多 CPU 資源。
因為 Oracle 中有虛擬列功能，更加方便 Oracle 的虛擬列遷移至 PostgreSQL 中。

待完善部分

一些功能目前尚不支持，但可能會作為增量功能在後續的版本中添加：

在虛擬列上創建索引或使用虛擬列。
虛擬列上也沒有唯一約束。
虛擬列上的擴展統計信息。
虛擬列上的外鍵約束。
虛擬列上的非空約束（支持檢查約束）。
ALTER TABLE / DROP EXPRESSION。
虛擬列不能具有域類型。
邏輯複製不支持虛擬列。

小結

虛擬列與普通列和存儲列有着本質的不同，因為虛擬列的值不佔磁盤空間，其獲取值的方式也與普通列和存儲列不同，普通列或存儲列需要從磁盤獲取數據，而虛擬列是通過動態計算獲取虛擬列的值。

四、UUID 功能增強：有序性與易用性提升

傳統 UUID 的無序性是其用作主鍵的主要痛點：

傳統 UUID（尤其是 v4）的完全隨機性是其作為數據庫主鍵的痛點：

UUID 隨機生成，插入位置不確定，導致索引樹頻繁分裂和重組，大幅降低寫入性能。
破壞聚簇索引（如 InnoDB）的物理存儲順序，增加磁盤 I/O。
範圍查詢和排序效率低下，性能低下。

UUIDv7 的關鍵突破：時間有序性架構設計

UUIDv7 通過在 UUID 的高位部分引入時間戳來解決生成 UUID 完全隨機的問題，使新生成的 UUID 能夠按照創建時間自然排序。這樣，B 樹索引可以像自增整數一樣進行順序插入，同時仍然保持 UUID 的全局唯一性和分佈式生成優勢。

該特性使 UUIDv7 作為主鍵具備以下突出優勢：

嚴格按照創建時間先後順序遞增。
減少索引碎片。
提高緩存命中率。
適合高併發插入和高效查詢的場景。

UUIDv7 的結構設計

字段	位數	説明
毫秒級 Unix 時間戳	48 位	Unix 時間戳（毫秒）
亞毫秒級時間戳分數（用於額外排序）	12 位	時間戳的微秒精度擴展
隨機數	62 位	隨機數或計數器
版本號	4 位	固定為 0111（v7）
變體	2 位	固定為 10（RFC 4122）

設計關鍵點解析：

高精度時間前綴(48 位): 精確到毫秒的 Unix 時間戳，確保 ID 嚴格按時間遞增（需 NTP 時鐘同步）。
尾部隨機位(62 位): 保證分佈式唯一性，避免 v1 版本的 MAC 地址泄漏風險。

有序性如何解決性能問題？

B-Tree 索引優化: 新生成的 UUIDv7 總是大於之前的值，因此被追加到索引尾部，避免中間節點分裂。
緩衝池友好: 順序寫入使新記錄集中在少數數據頁。當頁寫滿時，數據庫只需分配新頁追加，減少舊頁淘汰與磁盤 I/O。
範圍查詢加速: 時間有序性使 WHERE id > '2025-06-01' 可轉化為時間戳範圍過濾，大幅降低掃描範圍。

如何在 PostgreSQL18 中應用 UUIDv7

PostgreSQL 18 引入了多個新函數來支持 UUIDv7，方便生成、操作和提取 UUID 信息。

uuidv7()函數：用於生成新的 UUIDv7 值

-- 使用當前時間戳生成 UUIDv7
SELECT uuidv7();
-- 輸出示例: 0197f96c-b278-7f64-a32f-dae3cabe1ff0

-- 生成 1 小時前的 UUIDv7
SELECT uuidv7(INTERVAL '-1 hour');
-- 生成 30 分鐘後的 UUIDv7
SELECT uuidv7(INTERVAL '30 minutes');

uuidv4()函數：作為已有函數 gen_random_uuid() 的別名，便於和 uuidv7 一起使用

-- 兩者等價
SELECT gen_random_uuid();
SELECT uuidv4();

uuid_extract_timestamp()函數 : 該函數現在支持 UUIDv7（原本只支持 UUIDv1）

-- 從 UUIDv7 提取時間戳
SELECT uuid_extract_timestamp(uuidv7());
-- 示例輸出: 2025-09-18 12:20:49.409+00

uuid_extract_version()函數：用於檢測 UUID 的版本：

-- 檢查 UUID 版本
SELECT uuid_extract_version(uuidv7());  -- 返回 7
SELECT uuid_extract_version(uuidv4());  -- 返回 4

PostgreSQL 數據庫中使用 UUIDv7 作為主鍵：

--創建帶 UUIDv7 主鍵的表
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuidv7(),
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

這樣，每條新記錄的 id 都會自動分配一個按時間戳排序的 UUID。

--插入數據
INSERT INTO users (username, email)
VALUES ('alice', 'alice@example.com');
INSERT INTO users (username, email)
VALUES ('bob', 'bob@example.com');

-- 按 UUID 時間順序查看
SELECT id, username, uuid_extract_timestamp(id) as uuid_timestamp
FROM users
ORDER BY id;

性能優勢：

UUIDv7 的時間戳順序能顯著減少頁分裂和緩存失效，有效提升 B 樹索引效率。

--創建性能測試表
CREATE TABLE performance_test (
    id_v4 UUID DEFAULT uuidv4(),
    id_v7 UUID DEFAULT uuidv7(),
    data TEXT DEFAULT 'sample data'
);
--使用UUIDv7作為索引
CREATE INDEX idx_v4 ON performance_test (id_v4);
CREATE INDEX idx_v7 ON performance_test (id_v7);

批量插入後，你可以用 pg_statio_user_indexes 查看索引命中情況，UUIDv7 通常表現更優。

UUIDv7 自帶時間排序，大部分場景下顯著提升排序性能。

-- 利用 UUIDv7 自然排序
SELECT id_v7, data
FROM performance_test
ORDER BY id_v7
LIMIT 10;

相比 UUIDv4 的隨機順序，UUIDv7 查詢結果按創建順序返回，更直觀。

UUIDv7 最佳實踐

適合使用 UUIDv7 的場景：

多租户應用：可用 UUIDv7 做主鍵，併為 (tenant_id, id) 創建複合索引，既保持唯一性又能按時間排序。
分佈式系統：多個服務可獨立生成 UUIDv7，並且在全局範圍內仍能保持時間順序。

限制與注意事項

依賴系統時鐘：需啓用 NTP 等時間同步機制，避免時鐘漂移。
時間戳精度：UUIDv7 以毫秒為單位，在同一毫秒內生成多個 UUID，順序可能無法完全反映真實創建順序，但仍保持唯一性。
遷移規劃：從 UUIDv4 遷移到 UUIDv7 時，需要檢查應用邏輯、索引和外部依賴。

小結

PostgreSQL 18 對 UUIDv7 的支持，解決了 UUID 作為主鍵的性能瓶頸。UUIDv7 在保持全局唯一性的同時，具備類似自增整數的順序性，使 B 樹插入更高效，查詢更快。

對於需要分佈式、高併發和高性能的現代應用，UUIDv7 提供了一種兼顧唯一性和性能的實用解決方案。

五、EXPLAIN 增強：直觀呈現執行細節

PostgreSQL 18 對 EXPLAIN 命令進行了重大升級，通過提供更豐富、更直觀的執行計劃信息，讓數據庫開發者和 DBA 能夠更加輕鬆地進行查詢性能分析與優化。

自動緩衝區分析

EXPLAIN ANALYZE 現在默認包含 BUFFER 統計信息，無需手動添加 BUFFERS 選項：

共享命中(Shared Hits)：顯示從緩存中讀取的數據塊數量，反映內存使用效率。
共享讀取(Shared Reads)：標識必須從磁盤讀取的數據塊，幫助識別 I/O 瓶頸。
共享髒塊(Shared Dirtied)：針對數據修改操作，顯示被更改的塊數量。

精細化索引監控

新增索引掃描次數統計，讓開發者能夠精確瞭解索引使用效率：

-- 示例輸出顯示索引使用情況
Index Scan using orders_pkey on orders
Index Searches: 1  -- 明確顯示索引查找次數
Buffers: shared hit=2 read=2

增強的統計信息

支持小數行（ fractional row counts），提供更精確的行數估計。
為 Material、Window Aggregate、CTE 節點輸出內存和磁盤使用詳情。
在窗口函數中顯示詳細的參數信息。
為 Parallel Bitmap Heap Scan 顯示 worker 緩存統計。
輸出禁用節點。
輸出 WAL 緩衝區信息。

基礎查詢分析

-- 創建測試表
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL
);
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- 插入測試數據
INSERT INTO orders (customer_id, order_date, total_amount)
SELECT
    (n % 10) + 1,
    CURRENT_DATE - (n % 365),
    (50 + (random() * 950))::decimal(10,2)
FROM generate_series(1, 50000) n;

-- 查看增強的執行計劃
EXPLAIN ANALYZE
SELECT *,sum(total_amount) OVER (PARTITION BY customer_id)
FROM orders WHERE order_id>49900;

執行計劃：

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 WindowAgg  (cost=13.46..15.04 rows=99 width=50) (actual time=0.630..0.745 rows=100.00 loops=1)
   Window: w1 AS (PARTITION BY customer_id)
   Storage: Memory  Maximum Storage: 17kB
   Buffers: shared hit=5 read=2
   ->  Sort  (cost=13.30..13.55 rows=99 width=18) (actual time=0.307..0.333 rows=100.00 loops=1)
         Sort Key: customer_id
         Sort Method: quicksort  Memory: 28kB
         Buffers: shared hit=5 read=2
         ->  Index Scan using orders_pkey on orders  (cost=0.29..10.02 rows=99 width=18) (actual time=0.089..0.174 rows=100.00 loops=1)
               Index Cond: (order_id > 49900)
               Index Searches: 1
               Buffers: shared hit=2 read=2
 Planning:
   Buffers: shared hit=64 read=22
 Planning Time: 2.080 ms
 Execution Time: 1.343 ms

執行計劃輸出洞察：

緩衝區使用情況（緩存命中 vs 磁盤讀取）：生成執行計劃時從緩存中訪問了 64 個共享緩衝區，從磁盤中讀取了 22 個緩衝區，執行時從緩衝區訪問了 5 個共享緩衝區，從磁盤讀取了 2 個緩衝區。
索引效率統計：執行了 1 次 orders_pkey 索引掃描，並從緩存中訪問了 2 個共享緩衝區，清晰地顯示了索引的使用效率。
窗口函數內存使用詳情：使用的 17kB 磁盤空間。
精確的行統計信息。
窗口函數的詳細參數。

WAL 日誌分析

EXPLAIN (ANALYZE, WAL)
INSERT INTO orders (customer_id, order_date, total_amount)
SELECT
    (n % 10) + 1,
    CURRENT_DATE - (n % 365),
    (50 + (random() * 950))::decimal(10,2)
FROM generate_series(1, 50000) n;

執行計劃：

QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
 Insert on orders  (cost=0.00..2000.00 rows=0 width=0) (actual time=767.116..767.118 rows=0.00 loops=1)
   Buffers: shared hit=299156 read=2 dirtied=500 written=501
   WAL: records=152158 bytes=10427828 buffers full=139
   ->  Subquery Scan on "*SELECT*"  (cost=0.00..2000.00 rows=50000 width=28) (actual time=5.742..336.699 rows=50000.00 loops=1)
         Buffers: shared hit=50013
         WAL: records=1516 bytes=150084 buffers full=2
         ->  Function Scan on generate_series n  (cost=0.00..1750.00 rows=50000 width=24) (actual time=5.460..227.650 rows=50000.00 loops=1)
 Planning Time: 0.114 ms
 Execution Time: 767.179 ms

WAL 統計：

監控寫入負載的日誌生成量：WAL 緩衝區生成 1516 條日誌，共 150084 個字節的數據。
診斷寫入性能瓶頸：緩衝區被寫滿了 2 次。

並行查詢優化

EXPLAIN (ANALYZE)
SELECT * FROM orders WHERE customer_id IN (1, 2, 3, 4, 5, 6);

執行計劃：

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=2752.40..10357.99 rows=327855 width=18) (actual time=22.375..121.296 rows=330000.00 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   Buffers: shared hit=3810
   ->  Parallel Bitmap Heap Scan on orders  (cost=2751.40..10029.13 rows=136606 width=18) (actual time=12.868..88.329 rows=110000.00 loops=3)
         Recheck Cond: (customer_id = ANY ('{1,2,3,4,5,6}'::integer[]))
         Rows Removed by Index Recheck: 53967
         Heap Blocks: exact=170 lossy=566
         Buffers: shared hit=3810
         Worker 0:  Heap Blocks: exact=387 lossy=957
         Worker 1:  Heap Blocks: exact=369 lossy=1055
         ->  Bitmap Index Scan on idx_orders_customer_id  (cost=0.00..2669.44 rows=327855 width=0) (actual time=21.219..21.220 rows=330000.00 loops=1)
               Index Cond: (customer_id = ANY ('{1,2,3,4,5,6}'::integer[]))
               Index Searches: 1
               Buffers: shared hit=266
 Planning:
   Buffers: shared hit=30
 Planning Time: 0.510 ms
 Execution Time: 158.523 ms

並行執行效率洞察：

每個工作進程的緩存統計詳情：worker 0 命中 387 個精確塊和 957 個有損塊，worker 1 命中 369 個精確塊和 1055 個有損塊。
精確塊與有損塊分析：出現有損塊説明可能 work_mem 太小導致 bitmap 無法精準定位元組。

技術優勢與價值

即時性能診斷

降低門檻：自動化的緩衝區統計讓初學者快速識別 I/O 問題。
深度洞察：為專家級用户提供更細粒度的性能數據。
全面覆蓋：單條命令獲取執行計劃、緩存使用、索引效率等多維信息。

優化指導

索引優化：通過精確的索引使用統計，避免過度索引或索引不足。
內存調優：根據有損塊出現頻率指導 work_mem 參數調整
查詢重寫：基於詳細的執行成本數據優化 SQL 語句結構

功能未來展望

儘管 PostgreSQL 18 的 EXPLAIN 增強帶來了顯著改進，但仍有一些方面可以進一步完善：

輸出可讀性：隨着信息量的增加，輸出變得更加複雜，可能需要更好的格式化或可視化工具支持
歷史對比：缺乏直接與歷史執行計劃對比的內置機制，使得性能迴歸分析仍需依賴外部工具
閾值警報：沒有內置機制對異常值（如異常高的緩衝區讀取）發出警告，需要手動分析
執行計劃可視化：文本形式的輸出在複雜查詢中仍難以直觀理解，需要第三方工具補充

小結

PostgreSQL 18 的 EXPLAIN 增強代表了數據庫可觀測性的重大進步。通過自動化收集關鍵性能指標並提供更深入的執行洞察，它顯著降低了查詢優化的門檻，同時為經驗豐富的 DBA 提供了更強大的分析能力。

六、OAuth 2.0 認證支持：築牢數據防護壁壘

在安全方面，IvorySQL 致力於加入多種國密認證功能來保障數據安全。而這次 PostgreSQL18 在身份認證方面繼續加強，引入對 OAuth 2 的支持。這是一種開放標準的授權協議，用於授權一個應用程序或服務訪問用户在另一個應用程序中的資源，而無需提供用户名和密碼。

該特性主要包含以下幾個核心要素：

OAuth2 驗證器框架：提供了一個可擴展的框架，使 PostgreSQL 能夠與 OAuth 2.0 提供程序集成。PostgreSQL 本身不實現具體的令牌驗證算法（如 JWT 驗證），而是將這項工作委託給一個外部共享庫 (*.so 文件)。
客户端認證支持：libpq（PostgreSQL 的 C 客户端庫）現在支持 OAuth 2.0 認證流程。
自定義驗證邏輯：通過回調機制允許實現自定義的令牌驗證和用户映射邏輯。

配置 OAuth 認證方式

服務端配置

選擇 OAuth 認證的方式與瀚高數據庫選擇國密認證的方式類似，需要通過在 pg_hba.conf 文件中指定 METHOD 為 oauth，開啓 OAuth 認證。

同時 OPTIONS 必須指定 issuer 和 scope 參數，除此之外還有幾個可選參數：validator、map、delegate_ident_mapping，以下是一個最簡配置示例：
```
local all test oauth issuer="http://127.0.0.1:9000" scope="openid postgre"
```
指定外部 OAuth 驗證器，在 postgresql.conf 文件中配置新提供的 oauth_validator_libraries 參數，配置內容為 OAuth 驗證器提供的庫文件。

客户端配置

客户端在連接時需要指定以下連接參數從而實現連接：

oauth_issuer：必要參數，HTTPS URL，是授權服務器的頒發者標識符。
oauth_client_id：必要參數，由授權服務器頒發的 OAuth 2.0 客户端標識符。
oauth_client_secret：可選參數，訪問 OAuth 授權服務器時要使用的客户端密碼。
oauth_scope：可選參數，發送到授權服務器的訪問請求的範圍，指定為 OAuth 範圍標識符的空格分隔列表。

認證實現原理

oauth 整體認證流程大致如下圖所示：

客户端（libpq）

PostgreSQL 實現了一個非阻塞的、基於狀態機的異步網絡客户端。狀態機包含 OAUTH_STEP_INIT、OAUTH_STEP_DISCOVERY、OAUTH_STEP_DEVICE_AUTHORIZATION、OAUTH_STEP_TOKEN_REQUEST、OAUTH_STEP_WAIT_INTERVAL 這幾個狀態。其核心原理包含以下幾個部分：

DISCOVERY：客户端從用户請求中獲取授權服務器元信息。
DEVICE_AUTHORIZATION：客户端向授權服務器發送請求，授權服務器返回 device_code 和 verification_uri。客户端輸出信息"Visit xxxxxx and enter the code: xxxxxx"，提示用户進行操作。
TOKEN_REQUEST 和 WAIT_INTERVAL：輪詢訪問授權服務器，直到用户完成授權，授權服務器返回 access_token 給客户端。
將獲取到的 access_token 設置到連接對象中。libpq 會將它作為密碼發送給 PostgreSQL 服務器，服務器端的 OAuth 驗證器會負責校驗這個令牌。

服務端

以下 PostgreSQL 服務端處理 OAuth 認證流程，同樣通過狀態機實現，但要比客户端簡單得多，總共分為 OAUTH_STATE_INIT、OAUTH_STATE_ERROR、OAUTH_STATE_FINISHED 三個狀態。以下是核心步驟：

首先解析客户端發送的消息，該消息格式遵循 RFC 7628 第 3.1 節部分。
從客户端消息中提取出純粹的 Bearer Token，並驗證其格式（是否為合法的 Base64 字符串）。
將提取出的令牌傳遞給驗證器模塊進行實質性的驗證。
- 驗證成功：狀態轉為 OAUTH_STATE_FINISHED，返回 PG_SASL_EXCHANGE_SUCCESS。進行建立連接的後續操作。
- 驗證失敗：生成一個符合 RFC 7628 第 3.2.2 節的 JSON 錯誤響應，告知客户端所需的 scope 和到哪裏獲取令牌。狀態轉為 OAUTH_STATE_ERROR，並返回 PG_SASL_EXCHANGE_CONTINUE，等待客户端發送最終的 KVSEP 來結束失敗的握手。

外部驗證器

外部驗證器通常需要處理以下事項：

令牌驗證：可以通過在線驗證和本地驗證兩種方式，由驗證器自行決定。在線驗證下驗證器通常將令牌發送到授權服務器專門的 Introspection Endpoint，授權服務器會返回一個 JSON 響應，告知令牌是否有效。本地驗證則需要驗證器內部實現一套驗證流程，本地驗證令牌的簽名和有效期。本地驗證的好處在於能夠快速響應，但缺點是無法實時檢測令牌撤銷。
身份映射：在驗證通過後，驗證器需要提取令牌中的唯一用户標識，並轉換為數據庫可理解的身份標識，也就是數據庫用户。
連接決策：如果令牌處於有效期並且存在相應的數據庫用户映射關係，則以該用户的身份創建會話連接。

優缺點剖析

優點：

OAuth2 提供了現代、標準化的身份驗證機制，提高了安全性。通過 OAuth2 認證，規避了傳統密碼認證在數據傳輸過程中暴露密碼導致的安全風險。
簡化了數據庫用户管理，支持統一的身份策略和訪問控制，提高了管理效率。

缺點：

相較於傳統的密碼認證，實現更加複雜，需要額外的配置和維護工作，包括 OAuth2 提供程序的設置和管理，提高了運維的複雜度。比如瀚高數據庫的國密認證功能，同樣在保障口令安全的同時僅需要通過非常簡單的配置即可使用。
依賴於外部 OAuth 提供程序的可用性和可靠性，若 OAuth 提供程序出現問題，可能會影響數據庫訪問。
每次連接可能需要額外的網絡請求來驗證令牌，可能會增加連接建立的時間，特別是在高併發場景下。

小結

PostgreSQL 18 引入的 OAuth2 支持是一個重要的安全增強功能，它允許組織使用現代的身份驗證機制來保護數據庫訪問。通過提供靈活的驗證器框架和回調機制，PostgreSQL 18 可以適應各種 OAuth2 部署場景和業務需求。

雖然 OAuth2 認證增加了系統的複雜性，但它提供了顯著的安全優勢，特別是在集中式身份管理和單點登錄方面。對於希望採用現代安全最佳實踐的組織來説，這是一個值得考慮的新功能。OAuth 認證並不與已有的認證方式衝突，在不希望進行繁瑣的配置時，仍然可以選擇類似於瀚高數據庫提供的國密認證等方式。

在實施 OAuth2 認證時，組織應該仔細評估其需求、現有基礎設施和技術能力，以確保成功部署和運維。同時應該考慮到性能、可用性和兼容性等因素，以提供最佳的用户體驗和系統可靠性。

總結

PostgreSQL 18 憑藉六大核心特性實現了性能、功能與安全性的全方位升級：

異步 I/O 突破了同步阻塞瓶頸，提升讀取密集型場景的吞吐量。
跳躍式掃描讓多列 B 樹索引在非前導列查詢中發揮高效作用。
虛擬生成列在存儲與計算間找到了靈活平衡，優化了開發體驗。
UUIDv7 解決了傳統 UUID 無序性帶來的性能痛點，兼顧唯一性與順序性。
EXPLAIN 增強為查詢優化提供了更直觀、細緻的執行洞察。
OAuth 2.0 認證則為數據安全築牢了現代防護屏障。

這些特性不僅滿足了當前數據庫在高性能、高併發、易開發、強安全等方面的需求，也為未來在跨平台適配、功能擴展等方向奠定了堅實基礎，進一步鞏固了 PostgreSQL 在開源數據庫領域的領先地位，為各類應用場景提供了更強大、更靈活的技術支撐。

IvorySQL 動態日志

@ivorysql_xiaozhuli

標簽

後端 (616)

人工智能 (486)

數據庫 (310)

程序員 (231)

開源 (159)

運維 (140)

機器學習 (105)

服務器 (101)

postgresql (89)

SQL (72)

github (66)

oracle (61)

動態

聚焦六大功能：PostgreSQL 18 新特性深度解析 - 動態 詳情

一、PG 異步 I/O（AIO）框架：邁出打破同步阻塞瓶頸的第一步

核心提升場景

使用方法

框架設計

對原有設施的修改

使用注意及未來展望

小結

二、 跳躍式掃描：讓 B 樹索引 “提速換擋”

核心提升場景

效果測試

表結構與索引

數據生成

通過 COPY 導入數據

查詢語句 使用複合索引的第二個列

PostgreSQL 17 執行計劃 選擇使用並行順序掃描

PostgreSQL 18 執行計劃選擇使用索引掃描，可以看出跳躍式掃描執行效率提升幅度非常大

使用注意

小結

三、 虛擬生成列：存儲與計算的 “靈活平衡”

基本語法

虛擬列用例

實現原理淺析

虛擬列的創建

虛擬列的插入或更新

虛擬列的查詢

適用場景

待完善部分

小結

四、UUID 功能增強：有序性與易用性提升

傳統 UUID 的無序性是其用作主鍵的主要痛點：

UUIDv7 的關鍵突破：時間有序性架構設計

UUIDv7 的結構設計

如何在 PostgreSQL18 中應用 UUIDv7

性能優勢：

UUIDv7 最佳實踐

限制與注意事項

小結

五、EXPLAIN 增強：直觀呈現執行細節

自動緩衝區分析

精細化索引監控

增強的統計信息

基礎查詢分析

WAL 日誌分析

並行查詢優化

技術優勢與價值

即時性能診斷

優化指導

功能未來展望

小結

六、OAuth 2.0 認證支持：築牢數據防護壁壘

配置 OAuth 認證方式

服務端配置

客户端配置

認證實現原理

客户端（libpq）

服務端

外部驗證器

優缺點剖析

小結

總結

Add a new 評論

聚焦六大功能：PostgreSQL 18 新特性深度解析 - 動態詳情

二、跳躍式掃描：讓 B 樹索引 “提速換擋”

查詢語句使用複合索引的第二個列

PostgreSQL 17 執行計劃選擇使用並行順序掃描

三、虛擬生成列：存儲與計算的 “靈活平衡”