PostgreSQL 18 异步 I/O 深度解析：从三明治困境到 3 倍性能跃迁

一、引言：那个让数据库等了二十年的"红灯"

作为一个在生产环境里和 PostgreSQL 打了多年交道的老家伙，每次看到大版本发布公告，我的第一反应通常是——这次又有多少"牙膏"？

但 PostgreSQL 18 不一样。认真看完官方发布说明和内核变更记录后，我可以负责任地说：这是 PostgreSQL 过去五年里最硬核的一次架构级革新，不是功能点堆砌，是 I/O 子系统的底层重构。

核心变化只有一个：异步 I/O（AIO）终于来了，顺序扫描场景性能最高提升 3 倍。

但如果你以为这只是"提速"这么简单，那就太低估这次变更的深度了。AIO 背后牵动的，是 PostgreSQL 二十年来的 I/O 模型设计痼疾——三明治问题。这个问题不解决，再多的并发优化都是沙滩上的楼。

本文我会从物理原理 → 内核架构 → 配置实战 → 性能调优 → 迁移踩坑五个维度，把 PostgreSQL 18 的 AIO 讲透。每一个技术点都配实战代码，不是那种念文档的"特性清单"。

二、背景：PostgreSQL 的 I/O 之痛——三明治问题是怎么形成的

2.1 同步 I/O 的本质：CPU 在等硬盘

在聊 AIO 之前，必须先搞清楚 PostgreSQL 之前的 I/O 模型到底有什么问题。

传统的同步 I/O 模式下，数据库发起一次磁盘读取请求后，进程会阻塞等待，直到数据从磁盘到达内存。这个过程在机械硬盘时代大约是 5~15 毫秒，对于每秒处理上千条 SQL 的数据库服务器来说，这个"等待时间"累积起来是灾难性的。

CPU: 我要数据！
HDD: 稍等，我在转...
CPU: (等待中，什么都做不了)
HDD: 好了给你
CPU: 谢谢，下一条...

这就是经典的I/O 等待问题。更要命的是，在云时代，很多存储介质（网络存储、对象存储、低成本 NVMe SSD）的 I/O 延迟本身就高，而且波动大，同步等待的代价成倍放大。

2.2 PostgreSQL 17 及之前的解法：操作系统预读

PostgreSQL 之前的做法是依赖操作系统的预读（readahead）机制。操作系统会根据文件的访问模式做线性预读——假设你读第 N 块，大概率下一个要读第 N+1 块，所以提前把 N+1 拉进内存。

这个思路在顺序扫描场景下效果不错。但问题来了：

操作系统不知道数据库的查询计划，不知道你接下来是要做全表扫描还是索引查找，更不知道 VACUUM 什么时候要清理哪些页面。 预读的准确率在很多实际工作负载下严重不足——预读了不需要的块，真正需要的块没预读到，整体效率反而下降。

2.3 三明治问题（The Sandwich Problem）

真正让 PostgreSQL 开发者头疼的，是 三明治问题，也叫双重等待。

在 PostgreSQL 的 buffer manager 中，一次顺序扫描的数据读取路径是这样的：

执行器请求数据页面
    ↓
Buffer Manager 向 OS 发起同步 read() 系统调用
    ↓
OS 发起 DMA 传输，进程被挂起（第一次等待）
    ↓
DMA 完成，OS 通知进程
    ↓
Buffer Manager 将数据复制到 PG buffer cache
    ↓
执行器终于拿到数据，开始处理
    ↓
处理完，继续请求下一批数据
    ↓
Buffer Manager 再次向 OS 发起同步 read()
    ↓
又是一次同步等待（第二次等待）

在 PostgreSQL 17 及之前，这个模式是这样的：

// PostgreSQL 17 及之前的 smgrread() 简化逻辑
void smgrread(SMgrRelation smgr, ForkNumber forknum, BlockNumber blocknum, char *buffer) {
    // 直接调用 OS 同步读，进程在这里阻塞等待
    if (FileRead(smgr->rd_fd, buffer, BLCKSZ) != BLCKSZ) {
        ereport(ERROR, ...);
    }
}

每个批次的读取之间，进程完全被 OS 调度出去，而 PostgreSQL 的 buffer manager 层面无法感知这种等待，导致当一个查询需要顺序扫描几万甚至几百万个页面时，等待时间会成倍叠加。这就是"三明治"的形象比喻——一次同步 I/O = 一片面包，执行逻辑 = 火腿，频繁的等待把整块肉饼切得支离破碎。

2.4 业界解法横向对比

在 PostgreSQL 拥抱 AIO 之前，业界已经有多种解法：

PostgreSQL 18 的 AIO 子系统，在用户空间实现了类似 io_uring 的批量化 I/O 提交和完成通知能力，并且做到了跨平台兼容。

三、核心解析：PostgreSQL 18 的 AIO 架构设计

3.1 整体架构：三层抽象

PostgreSQL 18 的 AIO 子系统分为三层：

┌─────────────────────────────────────┐
│   Executor Layer (执行器层)           │
│   Buffer manager 调用 smgr_read_async │
└────────────────┬────────────────────┘
                 │ 提交 I/O 请求
                 ▼
┌─────────────────────────────────────┐
│   AIO Subsystem (异步 I/O 子系统)     │
│   io_method + io_workers 管理        │
└────────┬───────────────┬────────────┘
          │               │
    ┌─────▼─────┐   ┌─────▼─────┐
    │ io_uring  │   │   worker  │
    │ (Linux)   │   │ (POSIX)  │
    └───────────┘   └───────────┘
          │               │
          └───────┬───────┘
                  ▼
┌─────────────────────────────────────┐
│   OS / Storage (操作系统 / 存储层)   │
└─────────────────────────────────────┘

3.2 io_method 配置参数

PostgreSQL 18 引入了核心配置参数 io_method，支持三种模式：

-- 查看当前 I/O 方法（默认 sync）
SHOW io_method;
 io_method 
----------
 sync

-- 可选值：
-- 1. sync        : 保持 PostgreSQL 17 及之前的行为（兼容模式）
-- 2. worker      : 基于 POSIX 线程池的异步 I/O（跨平台，macOS/Windows 也能用）
-- 3. io_uring    : Linux 5.1+ 原生 io_uring 接口（Linux 专属，性能最佳）

3.3 核心参数详解

PostgreSQL 18 的 AIO 有三个核心调优参数：

-- AIO 方法选择
ALTER SYSTEM SET io_method = 'io_uring';  -- Linux 推荐
ALTER SYSTEM SET io_method = 'worker';    -- 跨平台（macOS/Windows/FreeBSD）

-- AIO Worker 数量（io_method=worker 时生效）
-- 默认：4。建议值：CPU 核心数的 25%~50%
ALTER SYSTEM SET io_workers = 8;

-- 预读块数（AIO 提交时一次性预取多少块）
-- 默认：64（io_method=io_uring 时）
ALTER SYSTEM SET effective_io_concurrency = 200;  -- 已有参数，控制并行度

3.4 源码解析：从 smgrread 到 AIO 提交

以下是从同步 I/O 迁移到 AIO 的关键代码路径对比：

PostgreSQL 17 及之前的路径：

// src/backend/storage/smgr/smgr.c (简化)
static void
smgrread(SMgrRelation reln, ForkNumber forknum, BlockNumber blocknum, char *buffer)
{
    // 调用 FileRead → 系统调用 read() → 同步等待
    if (FileRead(reln->rd_fd, buffer, BLCKSZ) != BLCKSZ)
        ereport(ERROR, (errcode_for_file_access(),
                        errmsg("could not read block %u in relation %s",
                               blocknum, relpathperm(reln->smgr_rlocator))));
}

PostgreSQL 18 的 AIO 路径：

// src/backend/storage/smgr/smgr.c (PG 18 简化)
#include "storage/aio_internal.h"

// 提交异步 I/O 请求（立即返回，不阻塞）
void
smgr_read_async(SMgrRelation reln, ForkNumber forknum, 
                 BlockNumber blocknum, char *buffer,
                 AIOCompleteCB callback, void *callback_arg)
{
    AIORequest *request = aio_new_request();

    request->type = AIO_OP_READ;
    request->smgr_reln = reln;
    request->fnum = forknum;
    request->blocknum = blocknum;
    request->user_buffer = buffer;
    request->callback = callback;
    request->callback_arg = callback_arg;

    // io_uring 或 worker pool 负责实际 I/O
    aio_submit_request(request);
    // ⚡ 立即返回，执行器可以继续处理其他工作
}

// 等待 I/O 完成（轮询或事件驱动）
void
aio_wait_completion(AIORequest *requests[], int count, int *completed)
{
    // 在 io_uring 模式下，使用 io_uring_enter() 获取完成事件
    // 在 worker 模式下，等待 pthread_cond 信号
    // 返回实际完成的请求数
}

3.5 io_uring vs worker 模式深度对比

这是很多同学关心的问题：我应该用 io_uring 还是 worker？

// io_uring 模式的核心优势——零拷贝和最小 syscall
// src/backend/storage/aio/linux_aio_uring.c (PG 18 概念代码)

int io_uring_submit_and_wait(int ring_fd, int submitted) {
    // 单次 syscall 提交 N 个请求 + 等待 N 个完成
    // io_uring_enter() = 一次系统调用完成批量 I/O
    return syscall(SYS_io_uring_enter, 
                   ring_fd,            // ring fd
                   submitted,          // 需要提交的请求数
                   1,                  // 需要等待完成的最小数量（>=1 才进入内核）
                   IORING_ENTER_GETEVENTS, // flags
                   NULL,               // sig
                   _NSIG / 8);         // sig mask
}

// worker 模式：基于 POSIX pthread 的线程池
// src/backend/storage/aio/posix_aio_worker.c (PG 18 概念代码)

typedef struct {
    pthread_t thread;
    int       worker_id;
    sem_t     pending_sem;    // 待处理的信号量
    TAILQ_HEAD(, AIORequest) pending_queue;  // 待处理请求队列
} AIOWorker;

void *aio_worker_main(void *arg) {
    AIOWorker *worker = (AIOWorker *)arg;

    while (true) {
        sem_wait(&worker->pending_sem);  // 等待新任务
        AIORequest *req = dequeue_request(worker);

        // 线程内执行实际的同步 read()
        ssize_t ret = pread(worker->fd, req->buffer, 
                           BLCKSZ, req->offset);

        // 标记完成，通知主线程
        aio_mark_complete(req, ret);
    }
    return NULL;
}

性能差异分析：

实战建议：

-- Linux 生产环境：使用 io_uring
ALTER SYSTEM SET io_method = 'io_uring';
ALTER SYSTEM SET shared_buffers = '8GB';  -- 越大越好
ALTER SYSTEM SET effective_io_concurrency = 200;  -- io_uring 并发 I/O 数

-- macOS / Windows / 不支持 io_uring 的 Linux：使用 worker
ALTER SYSTEM SET io_method = 'worker';
ALTER SYSTEM SET io_workers = 8;  -- 建议 4~16，根据 CPU 核心数调整

四、代码实战：PostgreSQL 18 AIO 配置与验证

4.1 环境检查

首先确认你的 PostgreSQL 18 支持哪种 AIO 方法：

# 检查 PostgreSQL 18 版本
psql --version
# psql (PostgreSQL 18.4)

# 检查内核是否支持 io_uring
cat /proc/sys/kernel/version
uname -r
# Linux 5.15+ 通常支持

# 检查是否可用
# 在 PostgreSQL 18 中，执行：
psql -c "SELECT pg_config('version');"

4.2 完整配置脚本

-- ============================================
-- PostgreSQL 18 AIO 完整配置脚本
-- 适用于：Linux + io_uring 生产环境
-- ============================================

-- 1. 设置 AIO 方法（核心配置）
ALTER SYSTEM SET io_method = 'io_uring';

-- 2. 调整 buffer 配置以充分利用 AIO
-- AIO 对大缓冲区场景效果最显著
ALTER SYSTEM SET shared_buffers = '16GB';  -- 建议设置为 RAM 的 25%

-- 3. 顺序扫描并发度（已有参数，配合 AIO 效果更佳）
ALTER SYSTEM SET effective_io_concurrency = 200;  -- io_uring 支持高并发

-- 4. 随机页面读取并行度
ALTER SYSTEM SET random_page_cost = 1.1;  -- NVMe SSD 通常设为 1.1~1.2

-- 5. 启用并行查询（充分利用 AIO 带来的吞吐提升）
ALTER SYSTEM SET max_parallel_workers_per_gather = 4;
ALTER SYSTEM SET max_parallel_workers = 16;
ALTER SYSTEM SET parallel_tuple_cost = 0.01;

-- 6. WAL 相关（虽然当前 AIO 主要优化读，但写优化是下一步）
ALTER SYSTEM SET wal_buffers = '64MB';

-- 7. 确认配置生效
SELECT name, setting, unit, context
FROM pg_settings
WHERE name IN (
    'io_method',
    'io_workers', 
    'effective_io_concurrency',
    'shared_buffers'
)
ORDER BY name;

-- 8. 需要超级用户权限重新加载
SELECT pg_reload_conf();

-- 9. 验证 AIO 是否正常工作
-- 启动一个长查询，观察 I/O 等待时间是否显著下降
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT COUNT(*) 
FROM large_table 
WHERE created_at > '2024-01-01';

4.3 性能对比基准测试

-- ============================================
-- PostgreSQL 18 AIO 性能对比测试
-- 对比：sync 模式 vs io_uring 模式
-- ============================================

-- 创建测试表（模拟大规模顺序扫描场景）
CREATE TABLE IF NOT EXISTS benchmark_orders (
    id BIGSERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    unit_price NUMERIC(10, 2),
    created_at TIMESTAMP DEFAULT NOW(),
    region VARCHAR(50),
    status VARCHAR(20)
);

-- 插入 1000 万条测试数据
INSERT INTO benchmark_orders (customer_id, product_id, quantity, unit_price, created_at, region, status)
SELECT 
    (random() * 100000)::INTEGER,
    (random() * 10000)::INTEGER,
    (random() * 100)::INTEGER,
    (random() * 1000)::NUMERIC(10, 2),
    NOW() - (random() * INTERVAL '365 days'),
    (ARRAY['North', 'South', 'East', 'West', 'Central'])[floor(random() * 5 + 1)],
    (ARRAY['pending', 'completed', 'cancelled', 'refunded'])[floor(random() * 4 + 1)]
FROM generate_series(1, 10000000);

-- 创建索引
CREATE INDEX idx_orders_created_at ON benchmark_orders(created_at);
CREATE INDEX idx_orders_region_status ON benchmark_orders(region, status);

-- 收集统计信息
ANALYZE benchmark_orders;

-- 测试 SQL 1：全表顺序扫描
EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT COUNT(*) 
FROM benchmark_orders;

-- 测试 SQL 2：范围查询（触发顺序扫描）
EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT customer_id, SUM(quantity * unit_price) AS total_amount
FROM benchmark_orders
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY customer_id
ORDER BY total_amount DESC
LIMIT 100;

-- 测试 SQL 3：VACUUM 性能（当前 AIO 也优化了 VACUUM）
VACUUM ANALYZE benchmark_orders;

-- 对比测试：查看当前 io_method
SHOW io_method;
SHOW io_workers;

4.4 Python 自动化性能对比脚本

#!/usr/bin/env python3
"""
PostgreSQL 18 AIO 性能对比测试脚本
测试 sync / worker / io_uring 三种模式的性能差异
"""

import psycopg2
import time
import statistics
from typing import List, Dict

class PG18AIOBenchmark:
    def __init__(self, host='localhost', port=5432, database='test', user='postgres', password=''):
        self.conn_params = {
            'host': host, 'port': port, 'database': database,
            'user': user, 'password': password
        }
        self.conn = None

    def connect(self):
        self.conn = psycopg2.connect(**self.conn_params)
        self.conn.autocommit = True

    def set_io_method(self, method: str, workers: int = 4):
        """设置 AIO 方法"""
        cursor = self.conn.cursor()
        cursor.execute(f"ALTER SYSTEM SET io_method = '{method}'")
        cursor.execute(f"ALTER SYSTEM SET io_workers = {workers}")
        cursor.execute("SELECT pg_reload_conf()")
        cursor.close()
        # 重新连接以应用配置
        time.sleep(1)
        self.conn.close()
        self.conn = psycopg2.connect(**self.conn_params)
        self.conn.autocommit = True

    def get_current_io_method(self) -> str:
        """获取当前 I/O 方法"""
        cursor = self.conn.cursor()
        cursor.execute("SHOW io_method")
        result = cursor.fetchone()[0]
        cursor.close()
        return result

    def run_query(self, sql: str, iterations: int = 5) -> Dict:
        """执行查询并测量性能"""
        cursor = self.conn.cursor()
        times: List[float] = []
        buffers: List[int] = []

        for _ in range(iterations):
            cursor.execute(f"EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) {sql}")
            plan = cursor.fetchone()[0][0]
            
            # 提取执行时间和缓冲区命中
            execution_time = plan['Execution Time']
            buffers_hit = plan['Plan']['Shared Hit Blocks']
            buffers_read = plan['Plan']['Shared Read Blocks']
            
            times.append(execution_time)
            buffers.append(buffers_read)

        cursor.close()

        return {
            'mean_ms': statistics.mean(times),
            'median_ms': statistics.median(times),
            'stdev_ms': statistics.stdev(times) if len(times) > 1 else 0,
            'total_buffers_read': sum(buffers),
            'iterations': iterations
        }

    def benchmark_all_modes(self, sql: str) -> Dict[str, Dict]:
        """对比三种 I/O 模式的性能"""
        modes = {
            'sync': {'method': 'sync', 'workers': 0},
            'worker': {'method': 'worker', 'workers': 8},
            'io_uring': {'method': 'io_uring', 'workers': 0},
        }

        results = {}
        for mode_name, config in modes.items():
            try:
                print(f"\n>>> 测试模式: {mode_name}")
                self.set_io_method(config['method'], config['workers'])
                
                current = self.get_current_io_method()
                print(f"    当前 io_method: {current}")

                result = self.run_query(sql, iterations=5)
                results[mode_name] = result

                print(f"    平均耗时: {result['mean_ms']:.2f} ms")
                print(f"    中位耗时: {result['median_ms']:.2f} ms")
                print(f"    标准差:   {result['stdev_ms']:.2f} ms")

            except Exception as e:
                print(f"    模式 {mode_name} 测试失败: {e}")
                results[mode_name] = {'error': str(e)}

        return results

    def generate_report(self, results: Dict[str, Dict]) -> str:
        """生成性能对比报告"""
        report = ["\n" + "="*60]
        report.append("PostgreSQL 18 AIO 性能对比报告")
        report.append("="*60)

        # 找基准模式（sync）
        baseline = results.get('sync', {}).get('mean_ms', 1)
        
        for mode, data in results.items():
            if 'error' in data:
                report.append(f"\n{mode}: 测试失败 - {data['error']}")
                continue

            speedup = baseline / data['mean_ms'] if baseline else 1
            report.append(f"\n【{mode}】")
            report.append(f"  平均耗时:     {data['mean_ms']:.2f} ms")
            report.append(f"  中位耗时:     {data['median_ms']:.2f} ms")
            report.append(f"  标准差:       {data['stdev_ms']:.2f} ms")
            report.append(f"  总读取块数:   {data['total_buffers_read']}")
            if mode != 'sync':
                report.append(f"  相对加速:     {speedup:.2f}x {'✅' if speedup > 1 else '⚠️'}")

        return "\n".join(report)


if __name__ == '__main__':
    benchmark = PG18AIOBenchmark(
        database='postgres',
        user='postgres',
        password='your_password'
    )

    benchmark.connect()

    # 测试查询：大规模聚合（触发顺序扫描）
    test_sql = """
    SELECT region, status, COUNT(*), 
           SUM(quantity * unit_price) AS revenue
    FROM benchmark_orders
    GROUP BY region, status
    ORDER BY revenue DESC
    """

    results = benchmark.benchmark_all_modes(test_sql)
    print(benchmark.generate_report(results))

五、其他重量级新特性：PostgreSQL 18 不只是 AIO

5.1 Skip Scan：打破"最左前缀"诅咒

这是另一个被严重低估的特性。

在 PostgreSQL 17 及之前，(a, b, c) 联合索引对于 WHERE b = 42 这样的查询完全无法使用，只能走全表扫描。Skip Scan 让这类查询现在可以走索引了：

-- PostgreSQL 17 及之前：全表扫描 ❌
EXPLAIN SELECT * FROM orders WHERE b = 42;
-- Seq Scan on orders  (cost=0.00..185000.00 rows=5000)
--   Filter: (b = 42)

-- PostgreSQL 18：自动 Skip Scan ✅
EXPLAIN SELECT * FROM orders WHERE b = 42;
-- Index Scan using idx_a_b_c on orders
--   Index Cond: (b = 42)  ← 走索引了！

这个优化背后的原理很有意思——PostgreSQL 18 会在查询规划阶段动态生成等值约束，自动拆解查询：

-- 对于 WHERE b = 42，查询规划器会自动转化为类似：
SELECT * FROM orders WHERE a = 'value1' AND b = 42
UNION ALL
SELECT * FROM orders WHERE a = 'value2' AND b = 42
UNION ALL
...
-- 其中 a 的值是从索引中提取的所有唯一值

5.2 uuidv7()：时间有序 UUID 的正确姿势

-- 旧方式（UUIDv4，随机，无法排序）：
SELECT gen_random_uuid();
-- 550e8400-e29b-41d4-a716-446655440000 ❌ 无序

-- PostgreSQL 18：UUIDv7（时间戳 + 随机，天然有序）✅
SELECT uuid_generate_v7();
-- 0191a2b3-c4d5-6e78-90f1-234567890abc ✅ 前半部分是毫秒时间戳

-- 实战应用：聊天消息表（高并发写入 + 需要按时间排序）
CREATE TABLE messages (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    sender_id BIGINT NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 插入 10000 条（模拟高并发场景）
INSERT INTO messages (sender_id, content)
SELECT 
    (random() * 10000)::BIGINT,
    'Message content ' || generate_series
FROM generate_series(1, 10000);

-- 按 ID 排序就等于按时间排序，无需额外索引
SELECT * FROM messages 
ORDER BY id DESC 
LIMIT 100;
-- PostgreSQL 18 可以直接利用 B-tree 对 UUID 的顺序索引

5.3 RETURNING OLD/NEW：少一次查询

-- PostgreSQL 17 及之前：更新后想知道旧值，需要两次查询 ❌
UPDATE user_accounts 
SET balance = balance - 100 
WHERE user_id = 123 AND balance >= 100;

SELECT * FROM user_accounts WHERE user_id = 123;  -- 额外一次查询

-- PostgreSQL 18：RETURNING 一次搞定 ✅
UPDATE user_accounts 
SET balance = balance - 100 
WHERE user_id = 123 AND balance >= 100
RETURNING id, balance AS new_balance, OLD.balance AS old_balance;
-- ⚠️ 注意：OLD 和 NEW 在 UPDATE 的 RETURNING 中都可用
-- 这是 PG 18 新增的能力

5.4 pg_upgrade 保留统计信息：告别"冷启动"

-- PostgreSQL 17 及之前升级后的噩梦：
-- 升级完成 → 统计信息丢失 → ANALYZE 运行中 → 查询极慢 → 持续数小时

-- PostgreSQL 18：
-- 升级完成 → 统计信息保留 → 查询性能立即恢复 ✅

-- 验证：
-- 升级后立即查看统计信息是否保留
SELECT tablename, n_live_tup, n_dead_tup, last_analyze
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY n_live_tup DESC
LIMIT 10;

-- 对比升级前后的 ANALYZE 时间戳
-- PG 18 应该能看到 last_analyze 有历史值

5.5 OAuth 2.0 认证：企业 SSO 集成

-- postgresql.conf 配置示例
# 创建 OAuth 应用（以 GitHub OAuth 为例）
# 需要配置 pg_hba.conf：

# host all all 0.0.0.0/0 oauth oauth_client_id=your_client_id

-- 创建 OAuth 角色
CREATE ROLE app_user WITH LOGIN OAUTH PROVIDER github;
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_user;

-- 通过 OAuth token 连接
# psql "host=localhost dbname=mydb options='-c oauth_token=your_token'"

5.6 时间范围约束：数据完整性新维度

-- 电商场景：防止同一 SKU 在同一时间段有重复的促销活动
CREATE TABLE promotions (
    id SERIAL PRIMARY KEY,
    sku VARCHAR(50) NOT NULL,
    discount_rate DECIMAL(5,2) NOT NULL,
    valid_period DATERANGE NOT NULL
);

-- WITHOUT OVERLAPS 约束：时间段不能重叠
CREATE TABLE promotions (
    id SERIAL PRIMARY KEY,
    sku VARCHAR(50) NOT NULL,
    discount_rate DECIMAL(5,2) NOT NULL,
    valid_period DATERANGE NOT NULL,
    EXCLUDE USING gist (sku WITH =, valid_period WITH &&)
    -- && 是"重叠"操作符
    -- 这个约束保证：同一个 SKU 的有效时间段不能重叠
);

-- 测试：插入重叠时间段会报错
INSERT INTO promotions (sku, discount_rate, valid_period)
VALUES ('SKU-001', 0.2, '[2026-01-01, 2026-06-30)');

INSERT INTO promotions (sku, discount_rate, valid_period)
VALUES ('SKU-001', 0.3, '[2026-06-01, 2026-12-31)');
-- ERROR: conflicting key value violates exclusion constraint
-- "promotions_sku_valid_period_excl"
-- DETAIL: Key (sku, valid_period)=(SKU-001, [2026-06-01,2026-12-31)) 
--          conflicts with existing key (sku, valid_period)=(SKU-001, [2026-01-01,2026-06-30))

六、性能调优全景指南

6.1 调优决策树

我的数据库适合开启 AIO 吗？
│
├── 当前 io_method = 'sync' 吗？
│   └── 是 → 继续
│   └── 否 → 已启用其他模式，检查性能
│
├── 什么场景受益最大？（优先开启的场景）
│   ├── ✅ 大表顺序扫描（数据仓库、报表查询）
│   ├── ✅ VACUUM 大表（减少 I/O 等待时间）
│   ├── ✅ 位图堆扫描（Bitmap Heap Scan）
│   ├── ✅ 大批量 INSERT ... SELECT
│   └── ❌ 小表随机读（索引查询为主）
│       → 小表几乎全部在 shared_buffers，I/O 不是瓶颈
│
├── 你的存储类型？
│   ├── NVMe SSD → io_uring，性能提升 2~3x
│   ├── SATA SSD → io_uring，效果明显
│   ├── 网络存储（NAS/云盘）→ io_uring，效果显著
│   └── 机械硬盘 → 效果有限，优先优化索引和查询
│
└── 推荐配置
    ├── Linux 5.1+ → io_uring
    ├── Linux 5.1- → worker
    └── macOS/Windows → worker

6.2 系统级调优

#!/bin/bash
# Linux 系统级调优脚本（配合 PostgreSQL 18 AIO 使用）

# 1. 增加 io_uring ring buffer 大小（需 root）
# 默认 32，设置为 256 适合高并发场景
echo 256 > /proc/sys/fs/io-uring/max-depth 2>/dev/null || echo "io-uring not available"

# 2. 调整 I/O 调度器（SSD 使用 noop/deadline，NVMe 使用 none）
echo none > /sys/block/nvme0n1/queue/scheduler 2>/dev/null || true

# 3. 调整文件描述符限制
cat >> /etc/security/limits.conf << 'EOF'
postgres soft nofile 65536
postgres hard nofile 65536
postgres soft nproc 32768
postgres hard nproc 32768
EOF

# 4. 调整 vm dirty ratio（写密集型场景）
echo 15 > /proc/sys/vm/dirty_ratio
echo 5 > /proc/sys/vm/dirty_background_ratio

# 5. 验证
cat /proc/sys/fs/io-uring/max-depth 2>/dev/null || echo "N/A"

6.3 监控 AIO 效果

-- PostgreSQL 18 新增的 VACUUM 统计信息
-- 查看 VACUUM 耗时（PG 18 新增）
SELECT 
    schemaname,
    relname,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch,
    n_tup_ins,
    n_tup_upd,
    n_tup_del,
    n_live_tup,
    n_dead_tup,
    last_vacuum,
    last_autovacuum,
    vacuum_count,
    autovacuum_count,
    -- PG 18 新增：VACUUM 相关耗时
    vacuum_page_dirty,
    vacuum_page_free
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY n_live_tup DESC
LIMIT 20;

-- 查看 I/O 统计（EXPLAIN ANALYZE 的增强）
EXPLAIN (ANALYZE, BUFFERS, WAL, TIMING, SUMMARY)
SELECT * FROM benchmark_orders 
WHERE created_at > '2025-01-01';

-- PG 18 输出会包含：
-- Buffers: shared hit=123 read=45645  ← 读了多少块
-- I/O Timings: shared read=1234.56ms  ← I/O 耗时

七、升级避坑指南

7.1 兼容性检查清单

-- 升级前在 PG 17 执行
SELECT version();  -- 确认当前版本

-- 检查是否使用了 MD5 密码认证（PG 18 已废弃）
SELECT rolname, rolpassword IS NOT NULL AS has_password
FROM pg_authid
WHERE rolname NOT IN ('pg_read_all_settings', 'pg_write_all_settings');

-- 检查全文检索和 pg_trgm 索引（PG 18 改变了 collation 提供者）
SELECT indexrelname, indcollate[1] AS collate
FROM pg_index
WHERE indrelid IN (
    SELECT oid FROM pg_class WHERE relkind = 'r'
    AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')
)
AND (indcollate[1] IN ('C', 'POSIX') = false);

-- 检查 unlogged 分区表（PG 18 不允许了）
SELECT schemaname, relname, relpersistence
FROM pg_class
WHERE relpersistence = 'u'
AND relkind = 'p';  -- 'p' = partitioned table

-- 检查是否有触发器依赖 AFTER 触发器的角色
SELECT tgname, tgtype, tgenabled, 
       (SELECT rolname FROM pg_roles WHERE oid = tgowner) AS owner
FROM pg_trigger
WHERE tgtype & 65 != 0  -- AFTER 触发器
AND NOT tginternal;

7.2 pg_upgrade 加速

#!/bin/bash
# PostgreSQL 17 → 18 升级脚本（利用 PG 18 的新特性）

set -e

OLD_DATA="/var/lib/postgresql/17/data"
NEW_DATA="/var/lib/postgresql/18/data"
PG_OLD="/usr/lib/postgresql/17/bin"
PG_NEW="/usr/lib/postgresql/18/bin"

# 1. 在旧版本执行 ANALYZE（确保统计信息完整）
$PG_OLD/bin/psql -c "ANALYZE VERBOSE;"

# 2. 检查复制槽（避免 WAL 积压）
$PG_OLD/bin/psql -c "
SELECT slot_name, plugin, slot_type, active, restart_lsn
FROM pg_replication_slots;
"

# 3. pg_upgrade（PG 18 新增：保留统计信息 + --swap 加速）
$PG_NEW/bin/pg_upgrade \
    --old-datadir=$OLD_DATA \
    --new-datadir=$NEW_DATA \
    --old-bindir=$PG_OLD/bin \
    --new-bindir=$PG_NEW/bin \
    --jobs=8 \            # PG 18 新增：并行检查
    --link \              # 硬链接模式，速度更快
    --swap               # PG 18 新增：直接交换，减少磁盘空间需求

# 4. 升级后立即验证
$PG_NEW/bin/psql -c "
SELECT version();
SELECT pg_stat_reset();
SELECT name, setting FROM pg_settings 
WHERE name IN ('io_method', 'io_workers', 'effective_io_concurrency');
"

echo "✅ 升级完成！"
echo "📌 注意：pg_upgrade 现在保留了统计信息，无需长时间 ANALYZE"
echo "📌 请立即配置 AIO：ALTER SYSTEM SET io_method = 'io_uring';"

八、总结：PostgreSQL 18 到底值不值得升？

8.1 核心结论

8.2 升级建议

强烈推荐升级的场景：

• 使用 NVMe SSD 或云存储的生产数据库
• 有大规模数据分析/报表查询需求
• 经常执行大表 VACUUM（数据量大）
• 现有大量联合索引但查询不总是最左前缀

可以观望的场景：

• 小型应用（数据量 < 100GB，全在 shared_buffers）
• 对稳定性要求极高、不想有任何风险的核心 OLTP 系统
• 还在使用 PostgreSQL 13/14 的遗留系统（建议先升级到 17）

8.3 行动清单

-- 今晚就可以做的事：
-- 1. 下载 PostgreSQL 18.4，在测试环境跑基准测试
-- 2. 对比 sync vs io_uring 性能差异
-- 3. 跑兼容性检查脚本
-- 4. 在测试环境升级，验证应用兼容性
-- 5. 准备灰度升级方案（先升级只读副本）

-- 一周内的事：
-- 1. 生产环境小表测试，确认无问题
-- 2. 收集基线性能数据
-- 3. 执行生产升级
-- 4. 监控关键指标：AIO 提交成功率、I/O 等待时间

PostgreSQL 18 不是一个"凑合能用的过渡版本"，AIO 的引入是 PostgreSQL 二十年来 I/O 模型最重要的架构演进。 3 倍的性能提升、Skip Scan 的查询优化、统计信息保留带来的无缝升级体验——这三项加在一起，在 2026 年这个时间节点，已经让 PostgreSQL 18 成为所有数据工程师必须认真对待的版本。

别等了，先跑测试。