PostgreSQL 18 深度拆解：当 OLAP 数据库之王进入全栈内嵌时代——从 AIO 异步 I/O 到虚拟生成列、从 Skip Scan 到 OAuth 2.0 的工程全貌（2026）

前言

2025年9月25日，PostgreSQL 全球开发组正式发布了 PostgreSQL 18，这是该开源数据库历史上性能提升最为显著的大版本之一。2026年5月14日发布的 PostgreSQL 18.4 补丁版本，进一步完善了这些特性，并修复了多个安全漏洞（多个 CVE）。

对于我们这些天天和数据库打交道的工程师来说，PostgreSQL 18 不是一个简单的版本号递增——它代表着一套系统性的工程改进，涵盖了从底层 I/O 架构到 SQL 语义的每一个层级。新的 AIO 异步 I/O 子系统将顺序扫描性能提升至多 3 倍；Skip Scan 让多列 B-tree 索引的利用率大幅提升；虚拟生成列彻底改变了"计算列"的存储模型；uuidv7() 为分布式系统提供了时间有序的 UUID 生成方案；OAuth 2.0 让企业级 SSO 集成成为开箱即用的能力。

本文将从工程师视角出发，对 PostgreSQL 18 的核心技术改进进行深度拆解，配合完整的代码示例和生产级配置指南，帮助你快速掌握这些新特性，并在实际项目中落地。

一、背景：为什么 PostgreSQL 18 值得关注

1.1 PostgreSQL 的演进逻辑

PostgreSQL 自 1996 年发布 6.0 版本以来，已经走过了近 30 年。与商业数据库动辄数十年的历史相比，PostgreSQL 的独特之处在于：它始终保持着每年一个大版本、每季度一个补丁版本的稳定节奏，同时在每一个大版本中都引入了经过社区充分讨论的实质性改进。

从 PostgreSQL 10 的逻辑复制，到 PostgreSQL 12 的增量备份和覆盖索引（covering index），再到 PostgreSQL 16 的并行 COPY 和 COPY FROM 的列级权限控制，PostgreSQL 一直在"功能全面性"和"性能深度"两条线上同步推进。PostgreSQL 18 则将重心明显地向"性能基础设施"倾斜。

PostgreSQL 18 的核心改进可以归纳为以下几个方向：

• I/O 基础设施重构：引入异步 I/O（AIO）子系统，从根本上改变数据页读取的方式
• 索引利用率的范式提升：Skip Scan 让多列索引在更多场景下被有效利用
• 数据类型现代化：uuidv7() 和虚拟生成列补全了 PostgreSQL 在现代分布式场景下的工具箱
• 安全性增强：OAuth 2.0 原生支持和 MD5 认证的正式废弃
• 运维体验改善：pg_upgrade 保留优化器统计信息，主版本升级不再"失血"

1.2 版本生态现状（2026年7月）

截至 2026 年 7 月，PostgreSQL 的版本支持状态如下：

注意：PostgreSQL 14 将于 2026 年 11 月 12 日停止接收修复，强烈建议仍在使用 PostgreSQL 14 的团队开始规划升级路径。

二、AIO 异步 I/O 子系统：3 倍性能提升的底层逻辑

2.1 传统 I/O 模型的问题

在 PostgreSQL 18 之前，数据页的读取采用的是同步 I/O 模型。当执行器需要读取一批数据页时，流程如下：

1. 发起一次 read() 系统调用，等待数据从磁盘加载到内核缓冲区
2. 数据从内核缓冲区复制到用户空间（PostgreSQL 的 Buffer Pool）
3. 重复步骤 1-2，直到所有请求的页都就绪

这种模型的局限性在于：每一次 I/O 操作都会阻塞调用线程。对于顺序扫描（Sequential Scan）和位图堆扫描（Bitmap Heap Scan）这类需要读取大量连续页的场景，同步 I/O 意味着 CPU 在等待磁盘 I/O 完成期间被白白浪费。

在 HDD 时代，磁盘寻道时间（约 10ms）远大于数据传输时间，I/O 的主要瓶颈是磁盘本身，同步模型的效率损失并不明显。但在 NVMe SSD 普及的今天，I/O 延迟降至微秒级，同步模型的线程阻塞开销反而成为了主要瓶颈。

2.2 AIO 子系统的架构设计

PostgreSQL 18 引入的 AIO 子系统由 Andres Freund、Thomas Munro 等核心开发者在 Linux (io_uring)、macOS (IOCP) 和 Windows (IOCP) 上实现。其核心思想是：将多个 I/O 请求批量提交给操作系统内核，内核在后台异步完成数据传输，PostgreSQL 执行器无需等待即可继续处理其他工作。

新增了以下 GUC 参数来控制 AIO 行为：

-- io_method: 选择 I/O 方法
-- linux 下支持 'io_uring'（最高效），macOS 支持 'select'
SET io_method = 'io_uring';

-- io_combine_limit: 单次合并的 I/O 请求数量阈值
-- 默认 65536 字节，与默认 BLCKSZ 对齐
SET io_combine_limit = '1MB';

-- io_max_combine_limit: io_combine_limit 的上限
-- 防止单次合并过大导致内存压力
SET io_max_combine_limit = '16MB';

AIO 子系统的工作原理可以通过以下伪代码来理解：

// PostgreSQL 18 之前的同步扫描（伪代码）
for (int i = 0; i < num_pages; i++) {
    // 阻塞调用：等待 read() 完成
    read(fd, buffer[i], BLCKSZ);
    process_page(buffer[i]); // 只有 read() 返回后才执行
}

// PostgreSQL 18 的 AIO 扫描（伪代码）
for (int i = 0; i < num_pages; i++) {
    // 非阻塞提交：将请求放入 AIO 队列
    io_queue_append(aio_ctx, read_request[i]);
}

// 批量等待：一次性等待所有已提交请求完成
io_queue_wait(aio_ctx, num_pages);

for (int i = 0; i < num_pages; i++) {
    process_page(buffer[i]); // 此时数据已就绪
}

关键区别在于：数据页的读取请求被批量提交后，执行器在等待期间可以执行其他计算工作（如对已就绪的页进行处理），而不是简单地阻塞等待。

2.3 受益场景分析

AIO 子系统对以下场景的提升最为显著：

1. 大表顺序扫描（Sequential Scan）

这是 AIO 最直接的受益场景。在 NVMe SSD 上，128KB 顺序读的理论带宽约为 7GB/s，而单次同步 read() 的上下文切换开销约为 0.5-1μs。AIO 通过批量提交可以将每批 128KB 数据的平均开销从 0.5μs 降至接近 0.1μs。

-- 对大表执行顺序扫描，AIO 自动生效
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM orders 
WHERE order_date >= '2025-01-01' 
  AND order_date < '2026-01-01';

2. 位图堆扫描（Bitmap Heap Scan）

当查询条件通过索引定位到大量行时，位图堆扫描需要按物理顺序读取数据页。在 PostgreSQL 18 之前，这些页的读取是随机的（即使物理上接近），同步 I/O 限制了预读的有效性。AIO 让预读机制更加高效：

-- 大范围查询触发位图堆扫描
EXPLAIN (ANALYZE) 
SELECT customer_id, SUM(amount) 
FROM transactions 
WHERE category IN ('electronics', 'books', 'clothing')
GROUP BY customer_id;

3. VACUUM 操作

VACUUM 需要读取表中所有非全可见的页。AIO 让 VACUUM 在扫描阶段可以更高效地与后台 I/O 交互，减少对前台查询的干扰：

-- VACUUM 现在可以利用 AIO 提高扫描效率
VACUUM ANALYZE orders;

4. pg_dump 和 pg_restore

批量数据导出/导入场景同样受益于 AIO：

# pg_dump 利用 AIO 提高数据读取效率
pg_dump -h localhost -U postgres -d mydb -Fc -f backup.dump

# 在恢复端，COPY FROM 也会受益
pg_restore -h localhost -U postgres -d mydb -j 8 backup.dump

2.4 性能数据与实测建议

根据 PostgreSQL 官方测试和社区反馈，AIO 子系统在不同场景下的性能提升如下：

注意：AIO 的效果在 SSD 上尤为明显，在 HDD 上由于磁盘本身的随机访问瓶颈，AIO 的收益有限。在决定是否开启 AIO 前，请先在生产级硬件上用 pg_test_tperf 进行基准测试。

2.5 AIO 监控接口

PostgreSQL 18 新增了 pg_aios 系统视图，用于监控 AIO 子系统的运行状态：

-- 查看当前 AIO 使用情况
SELECT * FROM pg_aios;

-- 示例输出（伪）：
--  pid  | context_name  | file_handle  | operation | submitted | completed | in_progress
-- ------+---------------+--------------+-----------+-----------+-----------+-------------
-- 12345 | Seq Scan      | 16384/0/0    | read      | 128       | 128       | 0
-- 12346 | Bitmap Heap   | 16384/0/1    | read      | 64        | 63        | 1

-- 通过 pg_stat_bgwriter 观察 I/O 模式的整体变化
SELECT 
    checkpoints_timed,
    checkpoints_req,
    buffers_checkpoint * 8192 / 1024 / 1024 AS checkpoint_mb,
    buffers_clean * 8192 / 1024 / 1024 AS clean_mb,
    maxwritten_clean
FROM pg_stat_bgwriter;

三、Skip Scan（跳跃扫描）：让多列索引脱胎换骨

3.1 传统多列索引的困境

Skip Scan 可能是 PostgreSQL 18 中对日常查询影响最大的优化器改进。理解它之前，我们先看一个经典的"多列索引失效"场景：

-- 创建多列索引：(a, b, c)
CREATE INDEX idx_orders_status_date ON orders(customer_id, status, order_date);

-- 查询：只想按 status 和 date 过滤，跳过 customer_id
SELECT * FROM orders 
WHERE status = 'shipped' 
  AND order_date >= '2026-01-01';

在 PostgreSQL 18 之前，这个查询无法使用上述多列索引，因为 B-tree 索引要求前缀匹配。即使 customer_id 列的选择性很差（很多重复值），优化器也只能选择全表扫描或独立的 status + order_date 索引的组合。

3.2 Skip Scan 的工作原理

Skip Scan 的核心思想来自 Oracle 的"跳跃式扫描"（Index Skip Scan），但实现方式更加现代化。PostgreSQL 18 的 Skip Scan 会在内部将查询：

SELECT * FROM orders WHERE status = 'shipped' AND order_date >= '2026-01-01';

转化为类似这样的执行策略：

对于 orders 表的 idx_orders_status_date 索引：
  第一步：提取 customer_id 的所有唯一值
         → 从索引中快速读取每个 customer_id 的第一个记录位置

  第二步：对每个 customer_id 值，执行：
         → 扫描 customer_id = 'CUST001' 的子范围
         → 在子范围内应用 status = 'shipped' AND order_date >= '...' 的条件

  第三步：合并所有 customer_id 的结果

-- 在 PostgreSQL 18 中，同一个查询计划发生了变化
EXPLAIN SELECT * FROM orders 
WHERE status = 'shipped' 
  AND order_date >= '2026-01-01';

-- PostgreSQL 18 的 EXPLAIN 输出（关键部分）
-- Index Scan using idx_orders_status_date on orders
--   Index Cond: ((order_date >= '2026-01-01') AND (status = 'shipped'))
--   Filter: (status = 'shipped')
--   Rows Removed by Filter: 0  ← 这条是关键：Skip Scan 有效过滤

3.3 适用条件与限制

Skip Scan 适用于以下条件：

1. 多列 B-tree 索引：目前仅支持 B-tree 类型的索引
2. 前缀列选择性差：第一列（或前几列）的唯一值数量较少
3. 后续列有强过滤条件：能够有效缩小扫描范围

-- 实际案例：电商系统查询某一类商品的所有订单
CREATE INDEX idx_products_category ON products(category, subcategory, brand);

-- 查询：只看某个子类别的所有品牌
-- PostgreSQL 18 前：无法使用 idx_products_category
-- PostgreSQL 18 后：Skip Scan 自动启用
SELECT p.*, pr.rating 
FROM products p
JOIN product_reviews pr ON p.id = pr.product_id
WHERE p.subcategory = 'wireless_headphones'
  AND p.brand IN ('Sony', 'Apple', 'Bose')
  AND pr.rating >= 4.5;

3.4 Skip Scan 与现有优化器的协同

PostgreSQL 18 还对优化器做了大量配套改进，Skip Scan 不是孤立的：

• 自动移除不必要的自联接（Self Join Elimination）：优化器会自动识别并删除语义上冗余的自联接
• OR 子句转数组：将 WHERE a = 1 OR a = 2 OR a = 3 转化为数组 WHERE a = ANY(ARRAY[1,2,3])，提高索引利用率
• GROUP BY 冗余列消除：如果 GROUP BY 列集合包含某唯一索引的所有列，则多余列会被自动移除

四、uuidv7()：时间有序 UUID 的工程实践

4.1 UUID 版本演进与分布式系统的痛点

UUID（通用唯一标识符）在分布式系统中几乎无处不在——微服务间的消息 ID、事件溯源的 Event ID、数据库分库分表的路由键——但传统 UUID（尤其是 UUID v4 随机型）存在一个根本性问题：随机性导致索引碎片化。

-- UUID v4：完全随机，写入时在 B-tree 索引中分布不均
SELECT gen_random_uuid();  
-- 示例：a1b2c3d4-e5f6-7890-abcd-ef1234567890
-- 问题：大量随机插入导致 B-tree 页频繁分裂，索引膨胀

-- UUID v1：基于时间戳 + MAC 地址
-- 问题：时间隐私泄露（MAC 地址可追踪）、无法保证全局单调递增

-- UUID v6：重新排列时间戳字段顺序
-- 问题：需要应用程序侧支持，数据库不原生支持

4.2 uuidv7() 的设计

PostgreSQL 18 引入了 uuidv7() 函数，遵循 RFC 9562（UUID v1-v7 规范）中的 UUID v7 规范：

-- 生成一个 UUID v7
SELECT uuidv7();

-- 示例输出：
-- 0194a3c4-7b90-7f00-8000-000000000000
-- |___________________| |__| |__|
--    48bit时间戳       ver 7  variant

-- 验证其时间有序性
SELECT 
    uuidv7() AS uuid1,
    pg_sleep(0.001),  -- 等待 1ms
    uuidv7() AS uuid2;

-- uuid1 < uuid2（时间单调递增）

4.3 uuidv7() 的内部实现原理

UUID v7 的结构如下：

 0                   1                   2                   3
 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|                         unix_ts_ms                            |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|          unix_ts_ms           |  ver  |       rand_a           |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|var|                       rand_b                              |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|                          rand_b                               |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

关键字段：

• unix_ts_ms（48bit）：Unix 毫秒时间戳，范围覆盖到公元 10889 年
• ver（4bit）：固定为 0111（二进制 7）
• rand_a（12bit）：随机数，用于在同一毫秒内生成不同值
• var（2bit）：变体位，固定为 10
• rand_b（62bit）：随机数，进一步扩展唯一性空间

4.4 生产级代码示例

-- 【场景一：微服务消息追踪】
CREATE TABLE events (
    id          UUID PRIMARY KEY DEFAULT uuidv7(),  -- 主键使用 uuidv7()
    service     TEXT NOT NULL,
    event_type  TEXT NOT NULL,
    payload     JSONB,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 由于 uuidv7() 按时间排序，事件日志天然按时间顺序存储
-- 对日志类场景的 B-tree 索引极为友好
CREATE INDEX idx_events_service_created ON events(service, created_at DESC);

-- 查询最近的事件（UUID 的时间有序性保证了结果与 created_at 一致）
SELECT * FROM events 
WHERE service = 'order-service'
ORDER BY id DESC  -- 可以直接按主键排序，等价于 ORDER BY created_at DESC
LIMIT 100;

-- 【场景二：分库分表路由键】
CREATE TABLE sharded_logs (
    id        UUID NOT NULL DEFAULT uuidv7(),
    shard_key TEXT NOT NULL,
    message   TEXT,
    PRIMARY KEY (id, shard_key)  -- 复合主键，id 的时间有序性有利于分区裁剪
) PARTITION BY HASH (shard_key);

-- 【场景三：与 UUID v4 的性能对比测试】
-- 创建两张结构相同的表，分别用 uuidv7() 和 uuid_generate_v4()
CREATE TABLE test_uuid7 (id UUID DEFAULT uuidv7(), data TEXT);
CREATE TABLE test_uuid4 (id UUID DEFAULT uuid_generate_v4(), data TEXT);

-- 写入 100 万条数据，测量索引膨胀率
INSERT INTO test_uuid7 (data) 
SELECT 'data_' || i FROM generate_series(1, 1000000) AS i;

INSERT INTO test_uuid4 (data) 
SELECT 'data_' || i FROM generate_series(1, 1000000) AS i;

-- 查看表和索引大小
SELECT 
    'uuidv7' AS type,
    pg_size_pretty(pg_total_relation_size('test_uuid7')) AS total_size,
    pg_size_pretty(pg_indexes_size('test_uuid7')) AS index_size,
    pg_size_pretty(pg_relation_size('test_uuid7')) AS table_size
UNION ALL
SELECT 
    'uuidv4',
    pg_size_pretty(pg_relation_size('test_uuid4') + pg_indexes_size('test_uuid4')),
    pg_size_pretty(pg_indexes_size('test_uuid4')),
    pg_size_pretty(pg_relation_size('test_uuid4'));

结论：在 100 万行数据测试中，uuidv7() 的 B-tree 索引膨胀率通常比 uuidv4() 低 30-50%，写入吞吐量高出 20-40%（具体数值取决于硬件和数据分布）。

五、虚拟生成列：计算列的存储革命

5.1 生成列的背景

PostgreSQL 从 12 版本开始支持生成列（Generated Column），但之前的实现是"存储型"（STORED）——计算结果会被持久化到磁盘，每次源数据变更时都需要重新计算并更新存储：

-- PostgreSQL 12-17 的存储型生成列
CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    subtotal    NUMERIC(12,2),
    tax_rate    NUMERIC(3,2) DEFAULT 0.10,
    tax         NUMERIC(12,2) GENERATED ALWAYS AS (subtotal * tax_rate) STORED,
    total       NUMERIC(12,2) GENERATED ALWAYS AS (subtotal + subtotal * tax_rate) STORED
);

-- 问题：tax 和 total 列需要占用磁盘空间
-- 更新 subtotal 时，需要同步更新 tax 和 total
-- 对于历史数据，STORED 生成列会消耗大量存储

5.2 虚拟生成列的实现

PostgreSQL 18 引入了虚拟生成列（VIRTUAL），计算仅在读取时发生，不占用磁盘存储：

-- PostgreSQL 18 的虚拟生成列
CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    subtotal    NUMERIC(12,2),
    tax_rate    NUMERIC(3,2) DEFAULT 0.10,
    -- 虚拟生成列：不占用存储空间，读取时实时计算
    tax         NUMERIC(12,2) GENERATED ALWAYS AS (subtotal * tax_rate) VIRTUAL,
    total       NUMERIC(12,2) GENERATED ALWAYS AS (subtotal * subtotal * tax_rate + subtotal) VIRTUAL,
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

-- 从 PostgreSQL 18 开始，GENERATED ALWAYS AS 默认就是 VIRTUAL
-- STORED 需要显式声明

INSERT INTO orders (subtotal, tax_rate) VALUES (1000.00, 0.13);

SELECT id, subtotal, tax_rate, tax, total FROM orders;
-- id=1, subtotal=1000.00, tax_rate=0.13, tax=130.00, total=1130.00

-- 更新源数据，虚拟列自动重新计算
UPDATE orders SET subtotal = 1500.00 WHERE id = 1;
SELECT id, subtotal, tax, total FROM orders;
-- tax 自动变为 195.00，total 变为 1695.00

5.3 虚拟 vs 存储：如何选择

-- 【生产实践：电商订单系统】
CREATE TABLE products (
    id          BIGSERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    base_price  NUMERIC(10,2),
    discount    NUMERIC(3,2) DEFAULT 0.00,
    -- 虚拟列：计算折后价（不占存储空间，适合频繁调价的场景）
    final_price NUMERIC(10,2) GENERATED ALWAYS AS (
        ROUND(base_price * (1 - discount), 2)
    ) VIRTUAL,
    -- 存储列：库存预警（写一次读多次，值得存储）
    low_stock_threshold INT DEFAULT 10,
    stock_qty    INT DEFAULT 0,
    is_low_stock BOOLEAN GENERATED ALWAYS AS (
        stock_qty <= low_stock_threshold
    ) STORED
);

-- 可以对虚拟列建立索引（用于过滤和排序优化）
CREATE INDEX idx_products_final_price ON products(final_price);
CREATE INDEX idx_products_low_stock ON products(is_low_stock) WHERE is_low_stock = true;

-- 查询：找出折后价在特定范围内且低库存的产品
SELECT name, base_price, discount, final_price
FROM products
WHERE final_price BETWEEN 50.00 AND 200.00
  AND is_low_stock = true
ORDER BY final_price;

5.4 虚拟生成列的约束

1. 函数限制：只能使用确定性函数（输入相同则输出相同）
2. 子查询限制：不能包含子查询
3. 序列限制：不能引用序列（nextval() 等）
4. 变异函数限制：不能使用可能随时间变化的函数（如 random()、NOW() 可以引用但要注意语义）

-- 合法：确定性函数
CREATE TABLE t1 (
    a INT,
    b INT,
    sum_v    INT GENERATED ALWAYS AS (a + b) VIRTUAL,      -- 正确
    product  INT GENERATED ALWAYS AS (a * b) VIRTUAL,        -- 正确
    abs_diff INT GENERATED ALWAYS AS (ABS(a - b)) VIRTUAL   -- 正确
);

-- 非法：使用非确定性函数
CREATE TABLE t2 (
    a INT,
    -- 错误：random() 是非确定性的
    random_val INT GENERATED ALWAYS AS (a + FLOOR(random() * 100)::INT) VIRTUAL
);

-- 合法：NOW() 是确定性的（事务开始时固定）
CREATE TABLE events_with_ts (
    event_type TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    -- 在事务内，event_timestamp 不会变化
    event_timestamp TIMESTAMPTZ GENERATED ALWAYS AS (created_at) VIRTUAL
);

六、OAuth 2.0 认证：企业 SSO 的最后一公里

6.1 PostgreSQL 认证体系的演进

在 PostgreSQL 18 之前，企业级用户如果想实现 SSO（单点登录），通常需要借助 PgBouncer 的 auth_query 或 auth_func，配合一个外部认证服务（LDAP、Kerberos、Radius）。这些方案各有不足：

• LDAP/Kerberos：适合传统 Active Directory 环境，但配置复杂
• 自定义 auth_query：需要维护一个额外的认证函数和外部服务
• 证书认证：适合机器间认证，不适合用户 SSO

6.2 PostgreSQL 18 的 OAuth 2.0 支持

PostgreSQL 18 新增了原生 OAuth 2.0 认证支持（RFC 8414），通过 pg_hba.conf 配置即可启用：

# pg_hba.conf - PostgreSQL 18

# OAuth 2.0 认证配置
# 支持 Authorization Code Flow（最标准的 Web SSO 流程）
host    all     all     0.0.0.0/0     oauth20 \
    client_id="postgresql-client" \
    issuer="https://auth.company.com" \
    jwks_file="/etc/postgresql/jwks.json" \
    userinfo_endpoint="https://auth.company.com/userinfo" \
    username_claim="sub"

-- 在 PostgreSQL 侧验证配置
SHOW authentication_timeout;  -- OAuth 令牌验证超时

-- 查看认证方法
SELECT * FROM pg_hba_file_rules WHERE auth_method = 'oauth20';

6.3 OAuth 认证的工作流程

PostgreSQL 18 的 OAuth 2.0 实现遵循标准的 Authorization Code Flow：

用户                    PostgreSQL                  身份提供商
 |                          |                              |
 |  psql -h db.company.com  |                              |
 |------------------------->|                              |
 |  发送 OAuth 访问令牌      |                              |
 |------------------------->|                              |
 |                          |  验证令牌签名（JWKS）          |
 |                          |----------------------------->|
 |                          |  获取用户信息（userinfo）     |
 |                          |----------------------------->|
 |                          |<-----------------------------|
 |                          |  提取 username_claim          |
 |  连接成功                 |                              |
 |<-------------------------|

6.4 JWT 和 JWKS 验证

OAuth 2.0 认证的核心是 JWT 令牌验证。PostgreSQL 18 内置了基于 JWKS 的公钥获取和验证逻辑：

# 模拟：使用 Python 生成一个符合 OAuth 2.0 的 JWT 令牌（用于测试）
import jwt
import datetime

# 模拟 JWKS（生产环境应从 issuer 的 .well-known/jwks.json 获取）
private_key = open("private_key.pem").read()

# 生成访问令牌
token = jwt.encode(
    {
        "sub": "alice@company.com",
        "iss": "https://auth.company.com",
        "aud": "postgresql-client",
        "exp": datetime.datetime.utcnow() + datetime.timedelta(hours=1),
        "iat": datetime.datetime.utcnow(),
        "scope": "postgres:read postgres:write"
    },
    private_key,
    algorithm="RS256",
    headers={"kid": "test-key-1"}
)

print(f"JWT Token: {token}")

# psql 连接时使用
# psql "host=db.company.com sslmode=require options='-c jwt_token={token}'"

6.5 权限映射与行级安全

OAuth 认证解决了"你是谁"的问题，接下来需要解决"你能做什么"的问题：

-- 创建基于角色的权限映射
CREATE ROLE oauth_read;
CREATE ROLE oauth_write;

-- OAuth 用户自动映射到对应角色
-- 在 pg_ident.conf 或通过系统函数配置：
-- username_claim="groups" → groups 声明包含 "pg-read" → 映射到 oauth_read

-- 行级安全策略
ALTER TABLE sensitive_data ENABLE ROW LEVEL SECURITY;

CREATE POLICY sensitive_data_read ON sensitive_data
    FOR SELECT
    TO oauth_read
    USING (
        -- 通过认证后，OAuth subject 可通过 current_user 访问
        created_by = current_user
        OR has_role(current_user, 'admin')
    );

-- 测试
SET ROLE oauth_read;
SELECT * FROM sensitive_data;  -- 只返回 created_by = current_user 的行

七、pg_upgrade 保留优化器统计：告别"升级后慢查询"

7.1 升级后的性能陷阱

长期以来，PostgreSQL 主版本升级后有一个令人头疼的问题：pg_upgrade 不会迁移优化器统计信息（pg_statistic、pg_stats）。这导致升级后的数据库在重建统计信息（ANALYZE）之前，优化器生成的执行计划质量极差：

-- 升级前：优化器知道 orders 表有 1 亿行，customer_id 的 NDV=1000
-- 执行计划：选择 Hash Join（正确）

-- 升级后（ANALYZE 之前）：
-- 执行计划：选择 Nested Loop Join（对 1 亿行来说是灾难）

在大型生产库上，一次完整的 ANALYZE 可能需要数小时甚至数天，这期间的业务系统可能经历严重的性能问题。

7.2 PostgreSQL 18 的改进

PostgreSQL 18 的 pg_upgrade 新增了统计信息迁移功能：

# PostgreSQL 18 中的 pg_upgrade 会自动迁移统计信息
# （在新版本中使用旧版本集群升级时）

cd /tmp/pg_upgrade_temp

# 方式一：自动迁移（PostgreSQL 18 默认行为）
pg_upgrade \
    --old-datadir=/var/lib/postgresql/17/data \
    --new-datadir=/var/lib/postgresql/18/data \
    --old-bindir=/usr/lib/postgresql/17/bin \
    --new-bindir=/usr/lib/postgresql/18/bin \
    --old-options="-c config_file=/etc/postgresql/17/postgresql.conf" \
    --new-options="-c config_file=/etc/postgresql/18/postgresql.conf" \
    --link  # 使用硬链接加速升级

# pg_upgrade 完成后，会自动将旧集群的统计信息导入新集群
# 不需要手动运行 ANALYZE

# 验证统计信息是否已迁移
psql -h localhost -U postgres -d postgres -c "
    SELECT tablename, n_live_tup, n_dead_tup, last_analyze 
    FROM pg_stat_user_tables 
    WHERE n_live_tup > 100000 
    ORDER BY n_live_tup DESC 
    LIMIT 10;
"

7.3 升级后验证检查清单

-- 检查点 1：确认统计信息已加载
SELECT 
    schemaname,
    tablename,
    n_live_tup,
    n_dead_tup,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE n_live_tup > 100000
ORDER BY n_live_tup DESC
LIMIT 10;

-- 检查点 2：对比执行计划是否合理
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.*, c.name as customer_name, SUM(oi.quantity * oi.unit_price) as order_total
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
WHERE o.created_at >= NOW() - INTERVAL '7 days'
GROUP BY o.id, c.name
ORDER BY order_total DESC
LIMIT 50;

-- 检查点 3：检查慢查询是否与升级前一致
-- 如果某条查询在升级后变慢，大概率是统计信息未迁移或执行计划退化

八、其他重要改进速览

8.1 temporal constraints（时序约束）

PostgreSQL 18 支持在 PRIMARY KEY、UNIQUE 和 FOREIGN KEY 上添加时序约束，这是现代业务系统的核心需求：

-- 【场景：有效时间段内的唯一性约束】
-- 确保同一房间在同一时间段内不能被重复预订
CREATE TABLE room_bookings (
    id          BIGSERIAL PRIMARY KEY,
    room_id     INT NOT NULL,
    guest_name  TEXT NOT NULL,
    start_time  TIMESTAMPTZ NOT NULL,
    end_time    TIMESTAMPTZ NOT NULL,
    
    -- 时序唯一约束：同一 room_id 的时间不能重叠
    CONSTRAINT no_double_booking 
        UNIQUE (room_id) 
        OVERLAPS (start_time, end_time)
);

-- 这个约束等价于：
-- CHECK (NOT EXISTS(
--     SELECT 1 FROM room_bookings r2
--     WHERE r2.room_id = room_bookings.room_id
--       AND r2.id != room_bookings.id
--       AND r2.start_time < room_bookings.end_time
--       AND r2.end_time > room_bookings.start_time
-- ))

8.2 RETURNING 子句增强

RETURNING 子句现在支持 OLD 和 NEW 记录：

-- PostgreSQL 18 之前：RETURNING 只返回新值
UPDATE accounts
SET balance = balance - 100.00
WHERE id = 1
RETURNING id, balance;

-- PostgreSQL 18：可以同时返回更新前后的值
UPDATE accounts
SET balance = balance - 100.00
WHERE id = 1
RETURNING id, OLD.balance AS prev_balance, NEW.balance AS new_balance;

-- INSERT 操作
INSERT INTO audit_log (table_name, action, old_data, new_data)
SELECT 'orders', 'UPDATE', 
       jsonb_build_object('id', OLD.id, 'status', OLD.status),
       jsonb_build_object('id', NEW.id, 'status', NEW.status)
FROM UPDATE OF orders WHERE status = 'cancelled';

-- DELETE 操作
DELETE FROM sessions
WHERE last_activity < NOW() - INTERVAL '1 day'
RETURNING id, OLD.*;  -- 打印被删除的会话详情

8.3 MD5 密码认证正式废弃

-- PostgreSQL 18 会发出废弃警告
CREATE ROLE test_user WITH LOGIN PASSWORD 'secret123';

-- 错误日志中会出现：
-- WARNING:  MD5 password authentication for user test_user is deprecated
-- HINT:  Use SCRAM-SHA-256 or OAuth authentication instead.

-- 禁用 MD5 警告（仅建议在过渡期使用）
SET md5_password_warnings = off;

-- 推荐迁移路径
-- 1. 切换到 SCRAM-SHA-256（向后兼容，推荐）
ALTER SYSTEM SET password_encryption = 'scram-sha-256';
SELECT pg_reload_conf();

-- 2. 大规模迁移时使用批量脚本
-- 使用 pg_authid 系统表批量处理

8.4 initdb 默认启用数据校验和

# PostgreSQL 18 的 initdb 默认启用数据校验和
# 可以通过 --no-data-checksums 禁用
initdb --no-data-checksums

# 检查数据校验和状态
SELECT datname, checksum_version 
FROM pg_database;

# pg_controldata 显示
# pg_controldata -D /var/lib/postgresql/18/data
# Data page checksum version: 1

九、生产升级指南：从 PostgreSQL 17 到 18

9.1 升级前检查清单

-- 1. 检查扩展兼容性
SELECT name, default_version, installed_version, comment
FROM pg_available_extensions
WHERE installed_version IS NOT NULL
  AND default_version != installed_version;

-- 2. 检查长事务（升级期间不允许长事务）
SELECT pid, usename, application_name, state, 
       (now() - xact_start)::interval AS duration,
       query
FROM pg_stat_activity
WHERE state NOT IN ('idle')
  AND xact_start IS NOT NULL
  AND (now() - xact_start) > interval '10 minutes';

-- 3. 检查复制延迟
SELECT client_addr, state, 
       (sent_lsn - replay_lsn) AS replication_lag_bytes,
       (now() - reply_time)::interval AS last_reply_age
FROM pg_stat_replication;

-- 4. 备份（必须）
pg_basebackup -h primary-host -U repl -D /backup/pg18 -Ft -z -P -Xs

9.2 滚动升级策略

#!/bin/bash
# rolling_upgrade.sh - 零停机滚动升级脚本

set -euo pipefail

OLD_VERSION=17
NEW_VERSION=18
PRIMARY_HOST=localhost
REPLICA_HOSTS=("replica1" "replica2" "replica3")

echo "=== PostgreSQL ${OLD_VERSION} -> ${NEW_VERSION} 滚动升级 ==="

# 步骤 1：在所有节点安装 PostgreSQL 18
# （需要在升级前完成）

# 步骤 2：在从节点上执行 pg_upgrade
for replica in "${REPLICA_HOSTS[@]}"; do
    echo "[$replica] 执行 pg_upgrade..."
    ssh postgres@$replica "
        pg_ctl -D /var/lib/postgresql/${OLD_VERSION}/data promote || true
        pg_upgrade \
            --old-datadir=/var/lib/postgresql/${OLD_VERSION}/data \
            --new-datadir=/var/lib/postgresql/${NEW_VERSION}/data \
            --old-bindir=/usr/lib/postgresql/${OLD_VERSION}/bin \
            --new-bindir=/usr/lib/postgresql/${NEW_VERSION}/bin \
            --link
    "
done

# 步骤 3：主节点升级
echo "[Primary] 执行 pg_upgrade..."
pg_ctl -m fast -D /var/lib/postgresql/${OLD_VERSION}/data stop
pg_upgrade \
    --old-datadir=/var/lib/postgresql/${OLD_VERSION}/data \
    --new-datadir=/var/lib/postgresql/${NEW_VERSION}/data \
    --old-bindir=/usr/lib/postgresql/${OLD_VERSION}/bin \
    --new-bindir=/usr/lib/postgresql/${NEW_VERSION}/bin \
    --link

echo "[Primary] 启动 PostgreSQL ${NEW_VERSION}..."
pg_ctl -D /var/lib/postgresql/${NEW_VERSION}/data -l /var/log/postgresql/startup.log start

echo "[Primary] 验证统计信息迁移..."
psql -h localhost -U postgres -d postgres -c "
    SELECT count(*) AS tables_analyzed 
    FROM pg_stats 
    WHERE last_analyze IS NOT NULL;
"

echo "=== 滚动升级完成 ==="
echo "建议运行 ANALYZE 验证表统计信息的完整性"

9.3 升级后 A/B 验证

-- 使用 pg_store_plans 或 auto_explain 捕获慢查询
-- 并与升级前的执行计划进行对比

-- 创建升级前后执行计划的对比视图
CREATE TABLE plan_comparison (
    query_id      BIGSERIAL PRIMARY KEY,
    query_text    TEXT NOT NULL,
    old_plan      JSONB,
    new_plan      JSONB,
    old_duration  INTERVAL,
    new_duration  INTERVAL,
    created_at    TIMESTAMPTZ DEFAULT NOW()
);

-- 通过 pg_stat_statements 找出性能显著变化的查询
SELECT 
    query,
    mean_exec_time AS avg_duration_ms,
    stddev_exec_time,
    calls,
    total_exec_time,
    rows,
    CASE 
        WHEN old_mean_exec_time > 0 THEN
            ROUND((mean_exec_time - old_mean_exec_time) / old_mean_exec_time * 100, 2)
        ELSE NULL 
    END AS pct_change
FROM pg_stat_statements
WHERE length(query) < 2000
  AND mean_exec_time > 10
ORDER BY (total_exec_time * mean_exec_time) DESC
LIMIT 20;

十、总结与展望

PostgreSQL 18 是一个真正的"工程之年"版本。它没有引入激进的新语法或全新的功能模块，而是将重心放在了性能基础设施（AIO I/O 子系统）、索引利用效率（Skip Scan）和开发者体验（uuidv7()、虚拟生成列、OAuth 2.0）三个方向上。

对于已经在使用 PostgreSQL 的团队，升级到 PostgreSQL 18 的收益是明确的：

• AIO 子系统：对 NVMe SSD 用户，顺序扫描性能直接提升 2-3 倍，无需修改任何 SQL
• Skip Scan：多列索引的利用率显著提升，大量历史遗留的"索引失效"问题不攻自破
• uuidv7()：分布式系统的 UUID 存储效率大幅改善，索引膨胀率降低 30-50%
• pg_upgrade 统计信息迁移：主版本升级的阵痛期大幅缩短
• OAuth 2.0：企业 SSO 集成从"高难度工程问题"变为"配置文件级操作"

展望 PostgreSQL 19+，社区已经在讨论的方向包括：

• 更激进的向量搜索集成（当前通过 pgvector 扩展实现）
• SIMD 加速的字符串处理函数
• 进一步增强的分区表裁剪能力
• WAL 压缩效率的持续改进

作为工程师，我们的策略是：紧跟 LTS 版本（当前为 PostgreSQL 17），每两个大版本评估一次升级。PostgreSQL 18 的改进足够让人眼前一亮，是时候认真规划你的数据库升级路线图了。

参考资料

1. PostgreSQL 18.4 Release Notes: https://www.postgresql.org/docs/release/18.4/
2. PostgreSQL 18.0 Release Notes: https://www.postgresql.org/docs/release/18.0/
3. RFC 9562 - UUIDs (v1-v7): https://www.rfc-editor.org/rfc/rfc9562
4. OAuth 2.0 Authentication: https://www.postgresql.org/docs/current/auth-oauth.html
5. PostgreSQL Wiki - AIO: https://wiki.postgresql.org/wiki/AIO