编程 PostgreSQL 18 核心技术深度解析：从异步 I/O 到虚拟生成列，一次看懂 2026 年最重磅的数据库升级

2026-07-10 01:17:33 +0800 CST views 12

PostgreSQL 18 核心技术深度解析：从异步 I/O 到虚拟生成列，一次看懂 2026 年最重磅的数据库升级

2025 年 9 月 25 日，PostgreSQL 全球开发组正式发布了 PostgreSQL 18。这是继 PostgreSQL 16 引入 Deduplication 优化、PostgreSQL 17 带来 pg_walinspect 之后的又一次重大版本更新。

如果你是一名 DBA 或后端工程师，你可能已经在生产环境里跑了 PostgreSQL 16 或 17，心里在想：18 有什么值得我升级的？

答案很直接：PostgreSQL 18 这一次动的是底层架构。

异步 I/O 子系统重写了存储层与后端的 I/O 交互模式，读性能提升最高可达 3 倍；uuidv7() 解决了分布式系统里 UUID 的时间顺序问题；虚拟生成列让 computed column 从"写时计算"变成"读时计算"；OAuth 2.0 认证让 SSO 集成终于不用绕弯子；Temporal Constraints 则在数据库层面原生支持了"有效期约束"这一企业级需求。

这不是小修小补，是一次底层重新设计。

本文将从架构层面深入解析 PostgreSQL 18 的核心技术，配以可运行的代码示例，帮助你理解每一个新特性的设计意图、使用场景和迁移注意事项。

一、为什么 PostgreSQL 18 值得特别关注

在 PostgreSQL 的历史上，每个大版本都有其标志性特性：

PostgreSQL 9.x 时代，FDW（外部数据包装器）让 PostgreSQL 成为了数据联邦的中心
PostgreSQL 10 引入逻辑复制和水平分区的原生支持
PostgreSQL 12 改善了覆盖索引（Covering Index）和即时编译（JIT）
PostgreSQL 13 的增量备份和并行 Vacuum 解决了运维痛点
PostgreSQL 14 到 17 在 JSON、性能监控、序列优化等方向持续打磨

而 PostgreSQL 18 的标志性特性是：底层 I/O 架构重写 + 企业级功能补全。

这不是单一功能的突破，而是对存储层、执行引擎、监控系统和安全模块的全面升级。用一个词来概括 PostgreSQL 18 的设计哲学：「让大规模数据处理更专业」。

具体来说，PostgreSQL 18 的核心价值体现在三个维度：

性能维度：异步 I/O + 优化器自我进化，让 OLAP 查询和大规模 VACUUM 获得数量级提升
开发者体验维度：uuidv7()、虚拟生成列、Temporal Constraints 让业务建模更自然
运维安全维度：OAuth 认证默认化 + 数据校验默认启用 + MD5 认证废弃倒计时

接下来，我们逐一深入解析。

二、异步 I/O 子系统：重写存储层交互模式

2.1 同步 I/O 的历史包袱

在 PostgreSQL 18 之前，所有后端进程对磁盘的读写操作都是同步的：进程发起一个 read() 或 write() 系统调用，内核负责将数据从磁盘（或页缓存）送入用户缓冲区，在此期间进程阻塞等待。

这种模式有几个深层问题：

问题一：顺序扫描时的预读效率低。
PostgreSQL 的顺序扫描（Sequential Scan）需要读取表中连续的页面。当页面不在页缓存中时，内核需要从磁盘读取。但 PostgreSQL 的预读（readahead）依赖 posix_fadvise() 提示，而现代 NVMe SSD 的 IOPS 远超机械硬盘，内核的预读算法对 NVMe 的优化不足，导致预读要么过量（浪费带宽），要么不足（产生 IO 等待）。

问题二：Bitmap Heap Scan 无法并发 IO。
Bitmap Heap Scan 是 PostgreSQL 处理 WHERE col IN (...) 类查询的核心手段。它先扫描索引建立一个 bitmap（位图），再根据位图定位需要读取的页面。问题在于，读取这些页面时，进程是逐个发起的，无法利用现代存储设备的并发 IO 能力。

问题三：VACUUM 无法与查询并行 IO。
Autovacuum 在后台运行时，它需要读取大量表页面来识别死元组。这个过程与用户查询共享 IO 带宽，在 IO 密集型负载下，VACUUM 和查询会相互竞争，导致响应时间抖动。

2.2 异步 I/O 子系统的设计

PostgreSQL 18 引入的异步 I/O 子系统（AIO Subsystem）允许后端进程批量发起 IO 请求，然后异步等待结果，而不是逐个阻塞等待。

核心实现原理：

PostgreSQL 18 使用 Linux 的 io_uring 接口（在支持 io_uring 的平台上），以及 libaio 作为 fallback 方案。当 io_uring 可用时，PostgreSQL 18 将 IO 请求提交到 io_uring 的 submission queue（提交队列），然后继续处理其他工作（解析、计划、执行），等 IO 完成事件出现在 completion queue（完成队列）时再处理结果。

关键参数：

-- 查看当前的 IO 方法配置（PostgreSQL 18 新增）
SHOW io_method;

-- 设置异步 IO 方法（可选项：auto, posix, linux_aio, disable）
SET io_method = 'auto';

-- 控制批量 IO 的合并策略
-- io_combine_limit: 单次系统调用合并的相邻 IO 请求数上限
SHOW io_combine_limit;

-- 单个批次最大合并 IO 数
SHOW io_max_combine_limit;

-- 有效的 IO 并发数（PostgreSQL 18 默认从 1 提高到 16）
SET effective_io_concurrency = 32;
SET maintenance_io_concurrency = 8;

注意：effective_io_concurrency 和 maintenance_io_concurrency 的默认值从之前的 1 提高到了 16。PostgreSQL 18 认为现代硬件（NVMe SSD、多核 CPU）完全可以支持更高的并发 IO。

2.3 性能提升：实测数据解读

根据 PostgreSQL 全球开发组发布的数据和社区实测，异步 I/O 对以下操作有显著提升：

操作类型	提升幅度	原因
全表顺序扫描	1.5x ~ 3x	批量预读 + 减少系统调用
Bitmap Heap Scan	2x ~ 4x	并发读取多个不连续页面
VACUUM FULL	2x ~ 3x	与查询的 IO 竞争被缓解
批量 COPY INTO	1.3x ~ 2x	写入时批量提交，减少 fsync 次数

注意：这些数据是在 NVMe SSD + 多核 CPU 环境下测得的。在传统机械硬盘环境下，提升幅度会小很多，但仍然有效。PostgreSQL 18 会自动检测硬件能力并选择合适的 IO 策略。

2.4 pg_aios 系统视图：监控异步 IO

PostgreSQL 18 新增了 pg_aios 系统视图，让你可以实时查看异步 IO 的运行状态：

-- 查看当前所有活跃的异步 IO 句柄
SELECT * FROM pg_aios;

-- 示例输出：
--  handle_id | file_oid | request_type | state | offset | length 
-- -----------+----------+-------------+-------+--------+--------
--       1234 |    16384 | READ         | DONE  | 0      | 8192
--       1235 |    16384 | READ         | PENDING | 8192 | 8192

这个视图对 DBA 非常有用，可以诊断 IO 瓶颈：

-- 诊断 IO 瓶颈的查询
SELECT 
    pg_database.datname,
    COUNT(*) FILTER (WHERE state = 'PENDING') AS pending_ios,
    COUNT(*) FILTER (WHERE state = 'DONE') AS completed_ios,
    COUNT(*) AS total_ios
FROM pg_aios
JOIN pg_class ON pg_aios.file_oid = pg_class.oid
JOIN pg_database ON pg_class.relnamespace = pg_database.oid
GROUP BY pg_database.datname
ORDER BY pending_ios DESC;

2.5 代码示例：对比同步与异步 IO 行为

-- ============================================
-- PostgreSQL 18: 利用异步 IO 加速大表扫描
-- ============================================

-- 准备测试环境
CREATE TABLE large_table (
    id BIGSERIAL PRIMARY KEY,
    data JSONB NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 生成 1000 万行测试数据
INSERT INTO large_table (data)
SELECT 
    jsonb_build_object(
        'key1', md5(random()::text),
        'key2', (random() * 1000)::int,
        'key3', array_to_json(array_agg(random()::int) OVER ())
    )
FROM generate_series(1, 10000000);

-- 创建 GIN 索引（GIN 索引在 PostgreSQL 18 支持并行构建）
CREATE INDEX idx_large_table_data ON large_table USING GIN (data);

-- 旧版本：强制使用顺序扫描，观察 IO 等待
SET enable_seqscan = on;
SET enable_indexscan = off;
SET effective_io_concurrency = 1;  -- 同步 IO 模式

EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT * FROM large_table WHERE (data->>'key2')::int BETWEEN 500 AND 600;

-- PostgreSQL 18：启用异步 IO，再次查询
SET effective_io_concurrency = 32;  -- 启用异步 IO
SET io_method = 'auto';

EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT * FROM large_table WHERE (data->>'key2')::int BETWEEN 500 AND 600;

-- 对比两个执行计划的 Buffers: shared hit 和 time 信息
-- 预期：PostgreSQL 18 的异步 IO 模式下，total time 显著降低

三、uuidv7()：分布式系统的时间有序 UUID

3.1 UUID 的时间顺序问题

在分布式系统中，UUID（通用唯一标识符）是最常用的主键类型之一。相比自增整数，UUID 的优势在于：

全局唯一性：多节点生成不冲突
无中心化：不需要数据库提供单调递增序列
安全性：不暴露业务规模和时间顺序

然而，UUID v1、v4 等经典方案有一个严重缺陷：时间无序。

UUID v4（纯随机）的 bit 分布是完全随机的，插入数据库时会导致 B-tree 页面频繁分裂，索引膨胀，性能下降。

UUID v1（基于时间+节点 MAC）虽然有时间顺序，但：

包含节点 MAC 地址，有隐私泄露风险
在某些分布式场景下，时间精度不够高时仍会产生冲突

3.2 uuidv7 的设计原理

UUID v7（正式名称为 "Lexicographically Sortable Identifier"）是一种基于时间的 UUID，其核心设计原则是：前 48 位是 Unix 毫秒时间戳，后 80 位是随机数。

+----------------+----------------+
|  48-bit 毫秒时间戳 | 80-bit 随机数  |
+----------------+----------------+

这种设计的精妙之处在于：

时间有序：同一毫秒内生成的 UUID 按随机数排序；不同毫秒，毫秒时间戳在前，所以自然有序
无隐私问题：不包含 MAC 地址
高吞吐量：每个毫秒可以生成 2^80 个不重复 UUID
可截断排序：前 48 位即包含了足够的时间分辨率，可以按时间范围查询

3.3 PostgreSQL 18 的 uuidv7() 函数

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

-- 示例输出：
--            uuidv7            
-- ----------------------------------
--  0192c5a1-7d80-7c8b-8f3e-2b9a1c4f6e1d

-- 验证时间有序性
SELECT 
    uuidv7() AS uuid1,
    uuidv7() AS uuid2,
    uuidv7() AS uuid3;

-- 从 UUID v7 中提取时间戳
CREATE OR REPLACE FUNCTION uuid7_to_timestamp(u UUID)
RETURNS TIMESTAMPTZ AS $$
DECLARE
    ts_bytes BYTEA;
    ts_value BIGINT;
BEGIN
    ts_bytes := E'\\x00' || substring(u::text, 2, 12);
    ts_value := (get_byte(ts_bytes, 0)::bigint << 40) |
                (get_byte(ts_bytes, 1)::bigint << 32) |
                (get_byte(ts_bytes, 2)::bigint << 24) |
                (get_byte(ts_bytes, 3)::bigint << 16) |
                (get_byte(ts_bytes, 4)::bigint << 8)  |
                (get_byte(ts_bytes, 5)::bigint);
    RETURN to_timestamp(ts_value::double precision / 1000);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 使用示例
SELECT uuid7_to_timestamp(uuidv7()) AS extracted_time;

3.4 uuidv7 vs uuidv4 vs 自增 ID 的索引效率对比

-- ============================================
-- 索引效率对比：uuidv7 vs uuidv4 vs bigserial
-- ============================================

CREATE TABLE orders_uuidv7 (
    id UUID PRIMARY KEY DEFAULT uuidv7(),
    product_name TEXT NOT NULL,
    quantity INT NOT NULL
);

CREATE TABLE orders_uuidv4 (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    product_name TEXT NOT NULL,
    quantity INT NOT NULL
);

CREATE TABLE orders_serial (
    id BIGSERIAL PRIMARY KEY,
    product_name TEXT NOT NULL,
    quantity INT NOT NULL
);

-- 分析索引膨胀率
SELECT 
    'uuidv7' AS key_type,
    pg_relation_size('orders_uuidv7_pkey') / pg_relation_size('orders_uuidv7') AS index_ratio
UNION ALL
SELECT 
    'uuidv4' AS key_type,
    pg_relation_size('orders_uuidv4_pkey') / pg_relation_size('orders_uuidv4') AS index_ratio
UNION ALL
SELECT 
    'serial' AS key_type,
    pg_relation_size('orders_serial_pkey') / pg_relation_size('orders_serial') AS index_ratio;

-- 预期结果：
-- uuidv7: 索引大小约为表的 0.6x ~ 0.8x（时间有序，热数据局部性好）
-- uuidv4: 索引大小约为表的 1.2x ~ 1.5x（随机插入导致页面分裂）
-- serial: 索引大小约为表的 0.4x ~ 0.5x（完美的顺序插入）

3.5 分布式系统中的 uuidv7 最佳实践

-- ============================================
-- 生产环境：分布式订单系统使用 uuidv7
-- ============================================

CREATE TABLE distributed_orders (
    order_id    UUID PRIMARY KEY DEFAULT uuidv7(),
    user_id     UUID NOT NULL,
    product_id  UUID NOT NULL,
    quantity    INT NOT NULL CHECK (quantity > 0),
    total_price DECIMAL(12, 2) NOT NULL,
    status      TEXT NOT NULL DEFAULT 'pending',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 时间游标分页（比 OFFSET 分页在大数据量下快 100 倍）
CREATE OR REPLACE FUNCTION paginate_orders(
    p_cursor TIMESTAMPTZ,
    p_limit INT DEFAULT 20
)
RETURNS SETOF distributed_orders AS $$
BEGIN
    RETURN QUERY
    SELECT * FROM distributed_orders
    WHERE created_at < p_cursor
    ORDER BY created_at DESC
    LIMIT p_limit;
END;
$$ LANGUAGE plpgsql STABLE;

SELECT * FROM paginate_orders(
    p_cursor := NOW(),
    p_limit := 20
);

四、虚拟生成列：从写时计算到读时计算

4.1 生成列的基础概念

PostgreSQL 14 引入了生成列（Generated Column）功能，允许在表中定义"计算列"——列的值不是用户直接写入的，而是由表达式计算得出的：

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    price NUMERIC(10, 2),
    tax_rate NUMERIC(3, 2) DEFAULT 0.13,
    -- 生成列：总价 = 含税价
    price_incl_tax NUMERIC(10, 2) 
        GENERATED ALWAYS AS (price * (1 + tax_rate)) STORED
);

注意这里的 STORED 关键字：在 PostgreSQL 17 及之前，生成列必须是 STORED，意味着写入（INSERT/UPDATE）时计算并存储到磁盘。

4.2 STORED vs VIRTUAL：两种生成策略

特性	STORED 生成列	VIRTUAL 生成列
计算时机	INSERT/UPDATE 时计算并存储	查询时实时计算
磁盘占用	占用表空间	不占用表空间
计算开销	写入时慢，读取时快	写入时快，读取时慢
可建索引	可以	可以
适用场景	计算代价高、读取频繁	计算代价低、写入频繁

PostgreSQL 18 之前，只支持 STORED。PostgreSQL 18 引入了 VIRTUAL 生成列（也叫 "虚拟生成列"），默认行为也改为 VIRTUAL。

4.3 PostgreSQL 18 的虚拟生成列

-- PostgreSQL 18: 虚拟生成列（默认行为）
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    birth_date DATE NOT NULL,
    
    -- 虚拟生成列：查询时实时计算，不占用磁盘空间
    age INT GENERATED ALWAYS AS (
        DATE_PART('year', AGE(birth_date))
    ) VIRTUAL,
    
    -- 也可以显式指定 STORED（旧行为仍然有效）
    age_stored INT GENERATED ALWAYS AS (
        DATE_PART('year', AGE(birth_date))
    ) STORED
);

-- 插入测试数据（注意：age 列不需要用户提供值）
INSERT INTO employees (name, birth_date) VALUES
    ('张三', '1990-05-15'),
    ('李四', '1985-11-20'),
    ('王五', '2000-03-10');

SELECT id, name, birth_date, age, age_stored FROM employees;

-- 验证虚拟列不占用额外空间
SELECT 
    pg_size_pretty(pg_relation_size('employees')) AS total_size,
    pg_size_pretty(pg_column_size(age)) AS virtual_col_size,
    pg_size_pretty(pg_column_size(age_stored)) AS stored_col_size;
-- 虚拟列的列大小为 0（不存储）

4.4 虚拟生成列的高级应用：JSON 数据建模

虚拟生成列在处理 JSON/JSONB 数据时特别有用，可以在不占用额外空间的情况下提取常用字段：

-- ============================================
-- 电商场景：使用虚拟生成列提取 JSON 嵌套字段
-- ============================================

CREATE TABLE api_logs (
    id BIGSERIAL PRIMARY KEY,
    request_id UUID NOT NULL,
    raw_payload JSONB NOT NULL,
    received_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    
    -- 虚拟生成列：从 JSONB 中提取常用字段（查询时才计算）
    user_id UUID GENERATED ALWAYS AS (
        (raw_payload->>'user_id')::UUID
    ) VIRTUAL,
    
    endpoint TEXT GENERATED ALWAYS AS (
        raw_payload->>'endpoint'
    ) VIRTUAL,
    
    response_time_ms INT GENERATED ALWAYS AS (
        (raw_payload->>'response_time_ms')::INT
    ) VIRTUAL,
    
    status_code INT GENERATED ALWAYS AS (
        (raw_payload->'response'->>'status_code')::INT
    ) VIRTUAL,
    
    is_error BOOLEAN GENERATED ALWAYS AS (
        ((raw_payload->'response'->>'status_code')::INT >= 400)
    ) VIRTUAL
);

-- 直接查询虚拟列（无需显式 JSONB 操作）
SELECT 
    endpoint, 
    response_time_ms, 
    status_code,
    CASE WHEN is_error THEN 'FAIL' ELSE 'OK' END AS status
FROM api_logs
WHERE user_id = '550e8400-e29b-41d4-a716-446655440001'::UUID;

-- 在虚拟列上建索引（PostgreSQL 18 支持）
CREATE INDEX idx_api_logs_endpoint ON api_logs (endpoint);
CREATE INDEX idx_api_logs_response_time ON api_logs (response_time_ms);
CREATE INDEX idx_api_logs_error ON api_logs (is_error) WHERE is_error = true;

4.5 迁移策略：从 STORED 生成列迁移到 VIRTUAL

-- 检查现有生成列类型
SELECT 
    attrelid::regclass AS table_name,
    attname AS column_name,
    attgenerated AS generation_type  -- 's' = STORED, 'v' = VIRTUAL
FROM pg_attribute
WHERE attgenerated != '';

-- 迁移：ALTER TABLE 改 VIRTUAL（旧数据不需要重写）
ALTER TABLE products 
    ALTER COLUMN price_incl_tax 
    SET GENERATED ALWAYS AS (price * (1 + tax_rate)) VIRTUAL;

五、Skip Scan：多列索引的全面解锁

5.1 传统 B-tree 多列索引的限制

在 PostgreSQL 18 之前，B-tree 多列索引有严格的使用限制：只有当查询条件包含索引最左侧列时，索引才会被使用。

CREATE INDEX idx_orders_user_status ON orders (user_id, status, created_at);

-- 索引被使用的查询 ✅（最左前缀原则）
SELECT * FROM orders WHERE user_id = 'xxx' AND status = 'pending';

-- 索引无法使用的查询 ❌（缺少最左列）
SELECT * FROM orders WHERE status = 'pending';

-- PostgreSQL 18 之前，必须为 status 单独建索引
-- 这就是所谓的"索引爆炸"问题

5.2 Skip Scan 的工作原理

PostgreSQL 18 引入了 B-tree Index Skip Scan，它的工作原理如下：

当查询条件不包含索引最左侧列时，优化器会：

从索引根部开始，扫描所有不同的最左侧列值
对每个值，在索引内部"跳过"（Skip）到对应的区间
在每个区间内扫描匹配条件的记录

形象地说，这就像图书馆索引目录：以前你要找所有"计算机类"的书，必须先知道索书号的前缀；Skip Scan 让你可以直接翻到每一章的开头，再找你要的书。

5.3 Skip Scan 代码实战

-- ============================================
-- Skip Scan 实战：从"必须建多索引"到"一个索引走天下"
-- ============================================

CREATE TABLE blog_comments (
    id BIGSERIAL PRIMARY KEY,
    post_id UUID NOT NULL,
    author_name TEXT NOT NULL,
    content TEXT NOT NULL,
    is_spam BOOLEAN NOT NULL DEFAULT false,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 创建一个多列复合索引（PostgreSQL 18 之前，这对 spam 过滤无效）
CREATE INDEX idx_comments_post_author_spam 
    ON blog_comments (post_id, author_name, is_spam);

-- 插入测试数据
DO $$
DECLARE
    post_uuids UUID[] := ARRAY[gen_random_uuid(), gen_random_uuid(), gen_random_uuid()];
    authors TEXT[] := ARRAY['Alice', 'Bob', 'Charlie', 'David', 'Eve'];
BEGIN
    FOR i IN 1..500000 LOOP
        INSERT INTO blog_comments (post_id, author_name, is_spam, content)
        VALUES (
            post_uuids[1 + (i % 3)],
            authors[1 + (i % 5)],
            (random() < 0.05)::BOOLEAN,
            'Comment content ' || i
        );
    END LOOP;
END $$;

-- 场景一：查询某个作者的所有评论（PostgreSQL 17 及之前无法使用复合索引）
EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM blog_comments
WHERE author_name = 'Alice'
AND is_spam = false;
-- 执行计划中应出现 "Index Skip Scan on idx_comments_post_author_spam"

-- 场景二：统计每个作者的评论数（聚合查询中使用 Skip Scan）
EXPLAIN (ANALYZE, COSTS OFF)
SELECT author_name, COUNT(*) 
FROM blog_comments
WHERE is_spam = false
GROUP BY author_name;

5.4 Skip Scan 的性能边界

Skip Scan 不是万能的，以下场景需要注意：

-- 高基数列不适合 Skip Scan
-- 如果 author_name 有 50 万个不同值，Skip Scan 需要扫描 50 万个区间
-- 这种情况下，全表扫描可能更快

-- 判断方法：查看列的选择性
SELECT 
    author_name,
    COUNT(*) AS count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS percentage
FROM blog_comments
GROUP BY author_name
ORDER BY count DESC;

-- 对于高基数列，考虑局部索引
CREATE INDEX idx_comments_spam_only 
    ON blog_comments (author_name) 
    WHERE is_spam = true;

六、OAuth 2.0 认证：企业 SSO 的原生支持

6.1 PostgreSQL 认证的历史演进

PostgreSQL 长期支持多种认证方法：trust、reject、md5、scram-sha-256、peer、gssapi、sspi、ident、ldap、pam、cert、radius。

但原生 OAuth 2.0 认证一直是空白。对于使用 Okta、Azure AD、Keycloak 等 SSO 系统的企业来说，PostgreSQL 认证要么绕道 LDAP/PAM，要么放弃数据库层面的集中认证。

PostgreSQL 18 填补了这个空白。

6.2 OAuth 认证的工作原理

PostgreSQL 18 的 OAuth 认证基于 OAuth 2.0 Token Introspection 协议：

客户端                    PostgreSQL                    OAuth Server
  |                            |                              |
  |--- 发起连接 -------------->|                              |
  |                            |-- Introspect Token ---------> |
  |                            |<-- Token 信息 (user, scope) --|
  |<-- 连接成功 (用户=token中的sub) --|                         |

6.3 配置 OAuth 认证

# 在 pg_hba.conf 中添加 OAuth 认证规则
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# host    all             all             0.0.0.0/0               oauth

# 更完整的配置（pg_hba.conf）
host  all  all  0.0.0.0/0  oauth \
    "client_id=app-postgres-prod", \
    "client_secret=/etc/postgresql/oauth_client_secret", \
    "token_url=https://auth.company.com/oauth2/token", \
    "introspection_url=https://auth.company.com/oauth2/introspect", \
    "user_claim=preferred_username", \
    "scope=openid profile email database:read", \
    "ca_file=/etc/ssl/certs/ca-bundle.crt"

-- 设置角色映射
CREATE ROLE readonly_oauth;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_oauth;

6.4 OAuth vs 传统认证的安全对比

维度	MD5	SCRAM-SHA-256	LDAP	OAuth 2.0
密码传输	弱（MD5碰撞风险）	强（挑战响应）	N/A	无密码（Token）
SSO 支持	❌	❌	✅	✅
MFA 支持	❌	❌	✅（LDAP server）	✅
短期凭证	❌	❌	❌	✅
维护成本	低	低	高（LDAP服务器）	中
企业合规	❌（已废弃）	✅	✅	✅

⚠️ 重要提示：PostgreSQL 18 已将 MD5 认证标记为废弃（Deprecated）。CREATE ROLE 和 ALTER ROLE 在设置 MD5 密码时会发出警告。MD5 认证将在未来版本中移除。强烈建议迁移到 SCRAM-SHA-256 或 OAuth 2.0。

七、监控增强：从 pg_stat_activity 到 pg_stat_io

7.1 PostgreSQL 18 的监控改进概述

PostgreSQL 18 对监控系统的改进是全方位的，新增了大量统计视图和函数：

pg_stat_io：I/O 活动统计（新增字节级统计）
pg_stat_get_backend_io()：每个后端的 I/O 统计
pg_stat_progress_vacuum：VACUUM 延迟报告增强
pg_stat_get_backend_wal()：每个后端的 WAL 统计

7.2 pg_stat_io 的新字段

-- PostgreSQL 18 新增的字节级统计列
-- read_bytes：读取的字节数
-- write_bytes：写入的字节数
-- extend_bytes：表/索引扩展的字节数
-- wal_bytes：WAL I/O 字节数

-- 分析数据库的 I/O 模式
SELECT 
    context,
    operation,
    object_type,
    read_bytes,
    write_bytes,
    extend_bytes,
    ROUND(read_bytes * 100.0 / NULLIF(read_bytes + write_bytes, 0), 2) AS read_ratio
FROM pg_stat_io
WHERE context = 'buffer bulkread'
ORDER BY read_bytes DESC
LIMIT 10;

-- 查看 WAL I/O 活动
SELECT 
    context,
    operation,
    read_bytes,
    write_bytes,
    sync_bytes
FROM pg_stat_io
WHERE context LIKE 'wal%';

-- 查看每个后端的 WAL 活动
SELECT 
    pid,
    query,
    pg_stat_get_backend_wal(pid) AS wal_stats
FROM pg_stat_activity
WHERE state = 'active';

7.3 VACUUM 延迟报告

PostgreSQL 18 的 VACUUM VERBOSE 输出更加详细：

-- 查看 VACUUM 进度（新增的延迟列）
SELECT 
    pid,
    datname,
    relid::regclass,
    phase,
    heap_blks_total,
    heap_blks_scanned,
    heap_blks_vacuumed,
    total_vacuum_time,
    vacuum_delay_time,    -- 新增：VACUUM 等待时间
    total_analyze_time,
    analyze_delay_time    -- 新增：ANALYZE 等待时间
FROM pg_stat_progress_vacuum;

-- 创建 VACUUM 性能监控视图
CREATE VIEW vacuum_performance AS
SELECT 
    a.datname,
    c.relname AS table_name,
    p.phase,
    ROUND(p.heap_blks_scanned::numeric / NULLIF(p.heap_blks_total, 0) * 100, 2) AS scan_progress_pct,
    p.vacuum_delay_time AS total_delay_ms,
    p.total_vacuum_time AS total_vacuum_ms,
    ROUND(p.vacuum_delay_time * 1000.0 / NULLIF(p.total_vacuum_time, 0), 2) AS delay_ratio_pct
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity a ON p.pid = a.pid
JOIN pg_class c ON p.relid = c.oid
WHERE a.query LIKE 'VACUUM%';

SELECT * FROM vacuum_performance ORDER BY delay_ratio_pct DESC LIMIT 10;

八、Temporal Constraints：数据库原生的有效期约束

8.1 什么是 Temporal Constraints

Temporal Constraints（时态约束）是 PostgreSQL 18 引入的一个企业级特性，允许在 PRIMARY KEY、UNIQUE 和 FOREIGN KEY 约束上定义有效期。

传统数据库中，如果一个用户的邮箱地址变更了，UPDATE 操作会覆盖旧值，历史记录丢失。时态约束允许你同时保留新旧两条记录，并确保在指定的时间范围内只有一条有效记录。

8.2 Temporal Constraints 的语法

-- ============================================
-- Temporal Constraints 实战
-- ============================================

CREATE TABLE employees_temporal (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    department_id INT NOT NULL,
    valid_from TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    valid_until TIMESTAMPTZ NOT NULL DEFAULT 'infinity'
);

-- Temporal UNIQUE：同一个部门在有效期内不能有两个同名员工
-- 但部门变更后，新部门可以使用相同的名字
ALTER TABLE employees_temporal 
    ADD CONSTRAINT uq_employees_temporal_dept_name 
    UNIQUE (department_id, name) 
    WITH temporal VALID DURING (valid_from, valid_until);

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    valid_from TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    valid_until TIMESTAMPTZ NOT NULL DEFAULT 'infinity'
);

ALTER TABLE departments
    ADD CONSTRAINT uq_dept_name_temporal
    UNIQUE (name) 
    WITH temporal VALID DURING (valid_from, valid_until);

-- Temporal FOREIGN KEY
ALTER TABLE employees_temporal
    ADD CONSTRAINT fk_department_temporal
    FOREIGN KEY (department_id, valid_from, valid_until) 
    REFERENCES departments (id, valid_from, valid_until)
    WITH temporal VALID DURING (valid_from, valid_until);

-- 插入测试数据
INSERT INTO departments (id, name) VALUES (1, 'Engineering');
INSERT INTO employees_temporal (name, department_id) VALUES ('张三', 1);

-- 尝试插入违反 Temporal Constraint 的数据（同一时间段重名，应被拒绝）
INSERT INTO employees_temporal (name, department_id, valid_from)
SELECT '张三', 1, '2025-01-01';

8.3 为什么要用 Temporal Constraints

没有 Temporal Constraints 时，SCD Type 2（Slowly Changing Dimension）实现非常繁琐：

-- 传统 SCD Type 2 实现（PostgreSQL 17 及之前）
CREATE TABLE employees_scd2_old (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    department_id INT NOT NULL,
    effective_from TIMESTAMPTZ NOT NULL,
    effective_to TIMESTAMPTZ NOT NULL DEFAULT 'infinity',
    is_current BOOLEAN NOT NULL DEFAULT true
);

-- 创建唯一约束（必须排除历史记录）
CREATE UNIQUE INDEX idx_unique_current 
    ON employees_scd2_old (department_id, name) 
    WHERE is_current = true;

-- 更新时必须开启事务，防止并发问题
BEGIN;
    UPDATE employees_scd2_old 
    SET effective_to = NOW(), is_current = false 
    WHERE is_current = true AND name = '张三';
    
    INSERT INTO employees_scd2_old (name, department_id, effective_from, effective_to, is_current)
    VALUES ('张三', 2, NOW(), 'infinity', true);
COMMIT;

-- PostgreSQL 18 Temporal Constraints 替代方案
-- 一条 ALTER TABLE 即可，无需手动管理 is_current 标志
-- 数据库自动保证时间不重叠

九、迁移到 PostgreSQL 18：完整指南

9.1 兼容性变更清单

PostgreSQL 18 引入了一些可能影响现有应用的兼容性变更，迁移前必须评估：

-- 1. initdb 默认启用数据校验和
-- 生产环境如果需要禁用校验和
-- initdb --no-data-checksums

-- 检查现有集群的校验和状态
SELECT datname, checksum 
FROM pg_control_checkpoint();

-- 2. MD5 密码认证已废弃
-- 检查哪些角色使用了 MD5 密码
SELECT rolname, rolpassword LIKE 'md5%' AS uses_md5
FROM pg_authid
WHERE rolpassword IS NOT NULL;

-- 迁移 MD5 密码到 SCRAM-SHA-256
-- ALTER ROLE user_name WITH PASSWORD 'new_secure_password';
-- PostgreSQL 会自动使用 SCRAM-SHA-256（默认优先方法）

-- 3. COPY FROM 对 \. 的处理更严格
-- CSV 文件中，\. 必须单独占一行

-- 4. AFTER 触发器以排队时的角色执行
SELECT 
    tgname AS trigger_name,
    relname AS table_name
FROM pg_trigger t
JOIN pg_class c ON t.tgrelid = c.oid
WHERE tgtype & 1 = 1;

9.2 pg_upgrade 的改进

PostgreSQL 18 的 pg_upgrade 在大版本升级时保留优化器统计信息，升级后无需长时间运行 ANALYZE：

# PostgreSQL 18 的 pg_upgrade 保留了统计信息
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 \
    --link

# 升级后验证
./analyze_new_cluster.sh
./delete_old_cluster.sh

9.3 推荐的升级路径

-- 升级前的全面检查
-- 1. 检查扩展兼容性
SELECT name, default_version, installed_version, comment
FROM pg_available_extensions
WHERE installed_version IS NOT NULL
ORDER BY name;

-- 2. 确认关键扩展是否支持 PostgreSQL 18
-- PostGIS、pgvector、pg_partman 等扩展需要确认有 18 版本

-- 3. 检查长时间运行的事务
SELECT pid, usename, state, query_start
FROM pg_stat_activity
WHERE state != 'idle'
  AND query_start < NOW() - INTERVAL '30 minutes';

-- 4. 检查复制延迟
SELECT client_addr, state, sent_lsn, replay_lsn
FROM pg_stat_replication;

十、性能优化实战：从参数调优到架构设计

10.1 PostgreSQL 18 推荐参数配置

-- ============================================
-- PostgreSQL 18 生产环境推荐参数（NVMe SSD + 64GB 内存）
-- ============================================

-- 10.1.1 异步 I/O 相关参数
ALTER SYSTEM SET io_method = 'auto';
ALTER SYSTEM SET effective_io_concurrency = 32;   -- PostgreSQL 18 默认 16
ALTER SYSTEM SET maintenance_io_concurrency = 8;
ALTER SYSTEM SET io_combine_limit = 128;

-- 10.1.2 内存参数
ALTER SYSTEM SET shared_buffers = '16GB';
ALTER SYSTEM SET work_mem = '256MB';
ALTER SYSTEM SET maintenance_work_mem = '2GB';
ALTER SYSTEM SET effective_cache_size = '48GB';

-- 10.1.3 VACUUM 参数
ALTER SYSTEM SET vacuum_max_eager_freeze_failure_rate = 10;
ALTER SYSTEM SET vacuum_truncate = on;

-- 10.1.4 WAL 参数
ALTER SYSTEM SET wal_buffers = '64MB';
ALTER SYSTEM SET min_wal_size = '2GB';
ALTER SYSTEM SET max_wal_size = '8GB';
ALTER SYSTEM SET track_wal_io_timing = on;

-- 10.1.5 并行查询参数
ALTER SYSTEM SET max_parallel_workers_per_gather = 8;
ALTER SYSTEM SET max_worker_processes = 32;
ALTER SYSTEM SET parallel_leader_participation = on;

-- 应用更改
SELECT pg_reload_conf();

-- 验证参数
SHOW effective_io_concurrency;
SHOW io_method;

10.2 利用新特性设计高性能表结构

-- ============================================
-- PostgreSQL 18 高性能表设计模板
-- ============================================

-- 场景：用户行为日志系统

CREATE TABLE user_events (
    -- 主键使用 uuidv7（时间有序，高写入吞吐量）
    event_id    UUID PRIMARY KEY DEFAULT uuidv7(),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    user_id     UUID NOT NULL,
    event_type  TEXT NOT NULL,
    session_id  UUID NOT NULL,
    payload     JSONB NOT NULL
) PARTITION BY RANGE (created_at);

-- 创建月度分区
CREATE TABLE user_events_2026_07
    PARTITION OF user_events
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

CREATE TABLE user_events_2026_08
    PARTITION OF user_events
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

-- 利用 Skip Scan 的复合索引（PostgreSQL 18+）
CREATE INDEX CONCURRENTLY idx_events_user_type_time 
    ON user_events (user_id, event_type, created_at DESC);

-- 利用 Skip Scan 在 event_type 上的独立索引
CREATE INDEX CONCURRENTLY idx_events_type 
    ON user_events (event_type, created_at DESC)
    WHERE event_type IN ('purchase', 'refund', 'signup');

-- 批量插入测试（验证 uuidv7 的高吞吐量）
DO $$
DECLARE
    batch_size INT := 10000;
    total_rows INT := 1000000;
    start_time TIMESTAMPTZ;
BEGIN
    start_time := clock_timestamp();
    
    FOR batch IN 0..(total_rows / batch_size - 1) LOOP
        INSERT INTO user_events (user_id, event_type, session_id, payload)
        SELECT 
            gen_random_uuid(),
            (ARRAY['page_view', 'click', 'purchase', 'refund', 'signup'])[1 + floor(random() * 5)::int],
            gen_random_uuid(),
            jsonb_build_object('page', '/product/' || floor(random() * 1000),
                               'duration_ms', (random() * 30000)::int)
        FROM generate_series(1, batch_size);
    END LOOP;
    
    RAISE NOTICE 'Inserted % rows in % ms', 
        total_rows, 
        EXTRACT(MILLISECONDS FROM clock_timestamp() - start_time);
END $$;

十一、总结：PostgreSQL 18 的核心价值

经过全文的深入分析，我们来总结 PostgreSQL 18 的核心价值：

架构层面的变革

异步 I/O 子系统不只是性能补丁，而是对 PostgreSQL 存储层交互模式的根本性重新设计。它将 NVMe SSD 的 IOPS 能力充分释放出来，让 PostgreSQL 在大规模数据处理场景下的竞争力大幅提升。

开发者体验的补全

**uuidv7()**解决了分布式系统 ID 的时间有序性问题，同时不损失无中心化的优势；
虚拟生成列让"计算列"从存储负担变成即时能力；
Temporal Constraints 让数据库原生支持 SCD Type 2 场景，不再需要复杂的应用层逻辑。

企业级功能的成熟

OAuth 2.0 认证让 PostgreSQL 可以无缝接入现代 SSO 生态；
MD5 废弃倒计时和数据校验和默认启用推动了安全基线的提升；
监控增强让 DBA 的诊断效率大幅提升。

迁移建议

如果你在跑 PostgreSQL 14/15/16，PostgreSQL 18 的异步 I/O + uuidv7() + Skip Scan 组合是强烈升级理由
如果你在跑 PostgreSQL 17，PostgreSQL 18 提供了uuidv7()、虚拟生成列、OAuth 认证等新功能，以及监控增强，建议评估后升级
升级前务必检查 MD5 密码使用情况和扩展兼容性

PostgreSQL 18 是近年来最值得升级的大版本之一。它的核心创新不在于某一个亮眼功能，而在于对存储层、执行引擎、安全认证和监控系统的全面升级，让 PostgreSQL 在处理大规模数据、企业级安全和现代云原生场景方面，都向前迈了一大步。

参考链接：

PostgreSQL 18 官方发布说明：https://www.postgresql.org/docs/18/release-18.html
PostgreSQL 18 异步 I/O 文档：https://www.postgresql.org/docs/18/runtime-config-resource.html#GUC-IO-METHOD
uuidv7 RFC：https://datatracker.ietf.org/doc/html/rfc-draft-ietf-uuidrev-06

复制全文生成海报 PostgreSQL 数据库 PostgreSQL 18 异步I/O uuidv7 OAuth 生成列 Skip Scan Temporal Constraints 性能优化