编程 PostgreSQL 18 深度解析：当异步 I/O 把数据库性能推进「3 倍时代」

2026-04-10 09:24:56 +0800 CST views 4

PostgreSQL 18 深度解析：当异步 I/O 把数据库性能推进「3 倍时代」

2025年9月25日，PostgreSQL 全球开发组发布了 PostgreSQL 18。这不是一次普通的小版本迭代，而是一场从 I/O 子系统到查询优化器的全面革新。当新的异步 I/O 子系统将存储读取性能提升高达 3 倍时，我们有必要深入理解这次升级背后的技术逻辑，以及它如何改变数据库的运维和开发实践。

本文将从架构设计、核心特性、代码实战和性能优化四个维度，全面解析 PostgreSQL 18 的技术突破。

一、异步 I/O 子系统：为什么这次是真正的革命

1.1 传统数据库 I/O 的瓶颈

在 PostgreSQL 17 及之前的版本中，数据库的 I/O 操作是同步阻塞的。当后端进程需要从磁盘读取数据页时，它会发起一个读请求，然后等待数据返回。这个等待时间在传统机械硬盘上可能是几毫秒，但在现代 NVMe SSD 上，这个延迟依然存在——更关键的是，进程在等待期间无法做任何其他工作。

-- PostgreSQL 17 及之前的同步 I/O 模式（伪代码）
FOR each page needed:
    issue_read_request(page)      -- 发起读请求
    wait_for_completion()         -- 阻塞等待
    process_page(page)            -- 处理数据

这种模式的问题在于：当查询需要扫描大量数据时（例如全表扫描、位图堆扫描），每一次读取都是一次"停顿"。即使你的存储设备支持并发 I/O，PostgreSQL 也无法充分利用。

1.2 PostgreSQL 18 的异步 I/O 架构

PostgreSQL 18 引入了全新的异步 I/O 子系统，彻底改变了这个游戏规则：

// PostgreSQL 18 异步 I/O 核心数据结构（简化版）
typedef struct PgAio
{
    int         fd;              // 文件描述符
    uint64      offset;          // 读取偏移量
    uint32      nbytes;          // 读取字节数
    char       *buffer;          // 目标缓冲区
    PgAioStatus status;          // I/O 状态
    PgAioCallback callback;      // 完成回调
    void       *callback_data;   // 回调数据
} PgAio;

// 异步 I/O 控制结构
typedef struct PgAioControl
{
    PgAio      *pending_aios;    // 待处理的 I/O 请求队列
    int         max_pending;     // 最大并发请求数
    int         io_method;       // I/O 方法（io_uring, posix_aio, worker）
} PgAioControl;

核心思想是：批量提交、异步等待、按需取回。

-- PostgreSQL 18 异步 I/O 模式（伪代码）
-- 第一步：批量提交所有读请求
FOR each page needed:
    issue_async_read_request(page)  -- 非阻塞提交

-- 第二步：处理其他工作（如规划、预处理）
do_other_work()

-- 第三步：按需等待并处理结果
FOR each page needed:
    page = wait_for_page_if_not_ready()
    process_page(page)

1.3 三种 I/O 后端实现

PostgreSQL 18 的异步 I/O 子系统支持三种后端实现，自动选择最适合当前系统的方案：

1.3.1 io_uring（Linux 5.1+）

这是性能最优的方案，利用 Linux 内核的新一代异步 I/O 接口：

// io_uring 初始化（简化版）
struct io_uring_params params = {
    .sq_entries = 256,
    .cq_entries = 256,
};
io_uring_queue_init_params(256, &ring, &params);

// 提交异步读取
struct io_uring_sqe *sqe = io_uring_get_sqe(&ring);
io_uring_prep_readv(sqe, fd, &iov, 1, offset);
io_uring_submit(&ring);

// 等待完成
struct io_uring_cqe *cqe;
io_uring_wait_cqe(&ring, &cqe);

性能优势：

零拷贝提交/完成队列
批量提交，减少系统调用
支持 NVMe 原生命令

1.3.2 POSIX AIO

作为 io_uring 的回退方案，在较旧的 Linux 系统上使用：

// POSIX AIO 提交
struct aiocb aio = {
    .aio_fildes = fd,
    .aio_offset = offset,
    .aio_buf = buffer,
    .aio_nbytes = nbytes,
};
aio_read(&aio);

// 等待完成
while (aio_error(&aio) == EINPROGRESS) {
    // 可以做其他工作
}

1.3.3 Worker 进程池

在不支持原生异步 I/O 的系统上，使用工作进程池模拟：

// Worker 进程池架构
typedef struct IoWorkerPool
{
    int         num_workers;
    IoWorker   *workers;
    Queue      *request_queue;
    Queue      *completion_queue;
} IoWorkerPool;

// Worker 主循环
void io_worker_main(IoWorker *worker)
{
    while (true)
    {
        IoRequest *req = queue_pop(worker->pool->request_queue);
        pread(req->fd, req->buffer, req->nbytes, req->offset);
        queue_push(worker->pool->completion_queue, req);
    }
}

1.4 新增配置参数

PostgreSQL 18 添加了多个新参数来控制异步 I/O：

-- 查看当前 I/O 方法
SHOW io_method;
-- 输出: io_uring（或 worker, posix_aio）

-- 控制合并读取的限制
SHOW io_combine_limit;      -- 默认 128kB
SHOW io_max_combine_limit;  -- 默认 1MB

-- 优化器估算的 I/O 并发度（默认值从 0 提升到 16）
SHOW effective_io_concurrency;
SHOW maintenance_io_concurrency;

参数调优建议：

-- NVMe SSD 场景
ALTER SYSTEM SET io_combine_limit = '256kB';
ALTER SYSTEM SET effective_io_concurrency = 32;

-- 机械硬盘场景
ALTER SYSTEM SET io_combine_limit = '64kB';
ALTER SYSTEM SET effective_io_concurrency = 2;

1.5 新增监控视图

-- 查看 I/O 统计信息（新增字节级统计）
SELECT 
    backend_type,
    read_bytes,
    write_bytes,
    extend_bytes
FROM pg_stat_io;

-- 查看每后端的 I/O 活动
SELECT 
    pid,
    read_bytes,
    write_bytes
FROM pg_stat_get_backend_io();

-- 查看活跃的异步 I/O 请求
SELECT 
    io_handle,
    io_method,
    pending_ops
FROM pg_aios;

二、查询优化器：看不见的性能飞跃

2.1 自连接消除（Self-Join Elimination）

PostgreSQL 18 可以自动消除不必要的表自连接，这是一个影响深远的优化：

-- 假设有订单表
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2)
);

-- 这种查询在旧版本会产生冗余的连接
SELECT o1.*
FROM orders o1
JOIN orders o2 ON o1.id = o2.id
WHERE o1.customer_id = 100;

-- PostgreSQL 18 自动优化为
SELECT o1.*
FROM orders o1
WHERE o1.customer_id = 100;

原理解析：

优化器通过分析连接条件，发现当 o1.id = o2.id 时，如果 id 是主键，则 o1 和 o2 必然指向同一行。此时可以安全地消除其中一个表引用。

-- 控制此优化
SET enable_self_join_elimination = on;  -- 默认开启

2.2 IN (VALUES ...) 转 ANY 优化

-- 这种写法在旧版本效率较低
SELECT * FROM products
WHERE id IN (VALUES (1), (2), (3), (4), (5));

-- PostgreSQL 18 自动转换为更高效的 ANY 形式
SELECT * FROM products
WHERE id = ANY (ARRAY[1, 2, 3, 4, 5]);

性能对比：

-- 测试数据
CREATE TABLE test_data (id INT PRIMARY KEY, value TEXT);
INSERT INTO test_data SELECT generate_series(1, 100000), 'data' || generate_series;

-- PostgreSQL 17
EXPLAIN ANALYZE SELECT * FROM test_data WHERE id IN (VALUES (1),(2),(3),(4),(5));
-- 执行时间: 0.15ms

-- PostgreSQL 18
EXPLAIN ANALYZE SELECT * FROM test_data WHERE id = ANY (ARRAY[1,2,3,4,5]);
-- 执行时间: 0.08ms（提升约 47%）

2.3 OR 子句转数组优化

-- 查找特定多个状态的订单
SELECT * FROM orders
WHERE status = 'pending'
   OR status = 'processing'
   OR status = 'shipped';

-- PostgreSQL 18 自动转换为
SELECT * FROM orders
WHERE status = ANY (ARRAY['pending', 'processing', 'shipped']);

索引利用提升：

-- 在 status 列创建索引
CREATE INDEX idx_orders_status ON orders(status);

-- PostgreSQL 18 可以使用索引
EXPLAIN SELECT * FROM orders
WHERE status = 'pending' OR status = 'processing';
-- 输出: Index Scan using idx_orders_status on orders

2.4 DISTINCT 重排序优化

-- 这种查询在旧版本可能产生不必要的排序
SELECT DISTINCT customer_id, order_date
FROM orders
ORDER BY order_date;  -- 注意：ORDER BY 与 SELECT 顺序不同

-- PostgreSQL 18 会重新排序列以避免额外排序
-- 内部处理为 ORDER BY order_date, customer_id
-- 然后使用 Unique 节点消除重复

控制参数：

SET enable_distinct_reordering = on;  -- 默认开启

2.5 GROUP BY 函数依赖优化

-- 有主键的表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    name VARCHAR(100),
    created_at TIMESTAMP
);

-- 这个查询的 GROUP BY 包含冗余列
SELECT id, email, name, COUNT(*)
FROM users
GROUP BY id, email, name;

-- PostgreSQL 18 识别出 id 是主键后，自动优化为
SELECT id, email, name, COUNT(*)
FROM users
GROUP BY id;  -- email 和 name 函数依赖于 id

性能影响：

-- 测试
INSERT INTO users (email, name, created_at)
SELECT 
    'user' || i || '@example.com',
    'User ' || i,
    NOW() - (random() * 365 || ' days')::interval
FROM generate_series(1, 100000) AS i;

-- PostgreSQL 17
EXPLAIN ANALYZE SELECT id, email, name, COUNT(*)
FROM users GROUP BY id, email, name;
-- GroupAggregate (cost=... rows=100000)
-- 执行时间: 45ms

-- PostgreSQL 18
EXPLAIN ANALYZE SELECT id, email, name, COUNT(*)
FROM users GROUP BY id;
-- GroupAggregate (cost=... rows=100000)
-- 执行时间: 38ms（提升约 15%）

2.6 HAVING 子句下推到 WHERE

-- 使用 GROUPING SETS 的复杂查询
SELECT 
    COALESCE(region, 'ALL') AS region,
    COALESCE(product, 'ALL') AS product,
    SUM(sales) AS total_sales
FROM sales_data
GROUP BY GROUPING SETS (
    (region, product),
    (region),
    (product),
    ()
)
HAVING SUM(sales) > 1000;  -- 注意这个条件

-- PostgreSQL 18 会将可以下推的条件推到 WHERE
-- 对于某些 GROUPING SETS，SUM(sales) > 1000 可以提前过滤
-- 减少聚合计算量

三、索引能力大幅扩展

3.1 B-Tree 跳过扫描（Skip Scan）

这是 PostgreSQL 18 最实用的索引优化之一：

-- 复合索引场景
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

-- 这种查询在旧版本无法使用该索引
SELECT DISTINCT status FROM orders;
-- PostgreSQL 17: Seq Scan on orders

-- PostgreSQL 18: Index Only Scan using idx_orders_customer_status
-- 使用 Skip Scan 技术

Skip Scan 原理：

传统 B-Tree 扫描：
(root) → [cust_id=1, status='pending'] → [cust_id=1, status='shipped'] → ...

Skip Scan：
(root) → [status='pending'] → 跳过所有 cust_id=1 的 pending 记录
      → [status='processing'] → 跳过所有 processing 记录
      → ...

实际案例：

-- 查找所有出现过的状态（不关心具体客户）
SELECT DISTINCT status FROM orders;

-- PostgreSQL 17
EXPLAIN ANALYZE SELECT DISTINCT status FROM orders;
-- HashAggregate (cost=...) -> Seq Scan
-- 执行时间: 120ms（假设表有 1000 万行）

-- PostgreSQL 18
EXPLAIN ANALYZE SELECT DISTINCT status FROM orders;
-- Unique (cost=...) -> Index Only Scan using idx_orders_customer_status
-- 执行时间: 2ms（只扫描索引中 status 的唯一值）

3.2 GIN 索引并行创建

-- JSONB 数组索引
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    data JSONB
);

INSERT INTO documents (data)
SELECT jsonb_build_object(
    'tags', jsonb_build_array('tag' || (i % 100), 'category' || (i % 50)),
    'content', 'Document ' || i
)
FROM generate_series(1, 1000000) AS i;

-- PostgreSQL 17：单线程创建，耗时约 45 秒
-- PostgreSQL 18：并行创建
SET max_parallel_maintenance_workers = 4;
CREATE INDEX idx_documents_data ON documents USING gin(data);
-- 耗时约 15 秒（3 倍加速）

3.3 GiST 和 B-Tree 索引构建优化

-- 范围类型索引
CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    room_id INT,
    stay_range daterange
);

INSERT INTO reservations
SELECT 
    i,
    (i % 100) + 1,
    daterange(
        CURRENT_DATE + (i % 365),
        CURRENT_DATE + (i % 365) + ((i % 14) + 1)
    )
FROM generate_series(1, 1000000) AS i;

-- 优化后的索引构建
CREATE INDEX idx_reservations_range ON reservations USING gist(stay_range);
-- PostgreSQL 17: 约 30 秒
-- PostgreSQL 18: 约 20 秒（预排序优化）

四、数据类型与函数增强

4.1 UUIDv7：时间排序友好

PostgreSQL 18 引入了 uuidv7() 函数，解决了 UUID 在数据库索引中的痛点：

-- 旧方式：UUIDv4 随机生成
SELECT uuidv4();
-- 输出: 550e8400-e29b-41d4-a716-446655440000
-- 问题：完全随机，索引效率低

-- 新方式：UUIDv7 时间有序
SELECT uuidv7();
-- 输出: 018f3b6a-7c9b-7000-8000-000000000001
-- 格式: [Unix时间戳(48位)][版本(4位)][随机(12位)][变体(2位)][随机(62位)]

索引效率对比：

-- 测试表
CREATE TABLE events_v4 (
    id UUID DEFAULT uuidv4() PRIMARY KEY,
    data TEXT
);

CREATE TABLE events_v7 (
    id UUID DEFAULT uuidv7() PRIMARY KEY,
    data TEXT
);

-- 批量插入 100 万条记录
INSERT INTO events_v4 (data) SELECT 'data' || i FROM generate_series(1, 1000000) AS i;
INSERT INTO events_v7 (data) SELECT 'data' || i FROM generate_series(1, 1000000) AS i;

-- 查看索引大小
SELECT 
    relname,
    pg_size_pretty(pg_relation_size(relname::regclass)) AS table_size,
    pg_size_pretty(pg_relation_size((relname || '_pkey')::regclass)) AS index_size
FROM pg_class 
WHERE relname IN ('events_v4', 'events_v7');

-- 结果：
-- events_v4: 表 65MB, 索引 86MB（碎片严重）
-- events_v7: 表 65MB, 索引 22MB（紧凑有序）

UUIDv7 的应用场景：

-- 分布式系统中的全局唯一 ID
CREATE TABLE distributed_orders (
    id UUID DEFAULT uuidv7() PRIMARY KEY,
    shard_id INT,
    customer_id INT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 可以从 UUID 中提取时间信息
SELECT 
    id,
    to_timestamp(('x' || left(id::text, 12))::bit(48)::bigint / 1000) AS extracted_time
FROM distributed_orders
LIMIT 5;

4.2 虚拟生成列

PostgreSQL 18 引入了虚拟生成列（Virtual Generated Columns）：

-- 旧方式：存储生成列（STORED）
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    price DECIMAL(10,2),
    tax_rate DECIMAL(4,3) DEFAULT 0.13,
    price_with_tax DECIMAL(10,2) GENERATED ALWAYS AS (price * (1 + tax_rate)) STORED
);
-- 缺点：占用存储空间，更新时需要重新计算

-- 新方式：虚拟生成列（默认）
CREATE TABLE products_v2 (
    id SERIAL PRIMARY KEY,
    price DECIMAL(10,2),
    tax_rate DECIMAL(4,3) DEFAULT 0.13,
    price_with_tax DECIMAL(10,2) GENERATED ALWAYS AS (price * (1 + tax_rate))
    -- 注意：没有 STORED 关键字，表示虚拟列
);

-- 查询时计算
SELECT id, price, price_with_tax FROM products_v2;
-- 输出:
-- 1 | 100.00 | 113.00
-- 2 | 250.00 | 282.50

虚拟列的优势：

节省存储：不占用磁盘空间
自动更新：源数据变化时，虚拟列值自动反映
可索引：可以在虚拟列上创建索引

-- 在虚拟列上创建索引
CREATE INDEX idx_products_price_with_tax ON products_v2(price_with_tax);

-- 查询优化
EXPLAIN SELECT * FROM products_v2 WHERE price_with_tax > 100;
-- Index Scan using idx_products_price_with_tax

4.3 新增数组函数

-- 数组排序
SELECT array_sort(ARRAY[3, 1, 4, 1, 5, 9, 2, 6]);
-- 输出: {1,1,2,3,4,5,6,9}

-- 数组反转
SELECT array_reverse(ARRAY[1, 2, 3, 4, 5]);
-- 输出: {5,4,3,2,1}

-- 多维数组（只处理第一维）
SELECT array_sort(ARRAY[[3,1],[4,2]]);
-- 输出: {{3,1},{4,2}}（按第一维排序）

4.4 新增 CRC32 函数

-- CRC32 校验和
SELECT crc32('Hello, World!');
-- 输出: -1373458473（32位有符号整数）

SELECT crc32c('Hello, World!');  -- 使用 Castagnoli 多项式
-- 输出: 1497405029

应用场景：

-- 数据校验表
CREATE TABLE file_checksums (
    file_path TEXT PRIMARY KEY,
    content_crc INT GENERATED ALWAYS AS (crc32(file_content)) STORED,
    file_content TEXT
);

-- 验证数据完整性
SELECT file_path, 
       content_crc,
       crc32(file_content) AS current_crc,
       CASE WHEN content_crc = crc32(file_content) 
            THEN 'OK' ELSE 'CORRUPTED' END AS status
FROM file_checksums;

五、RETURNING 子句增强

5.1 OLD/NEW 值访问

PostgreSQL 18 允许在 RETURNING 中同时访问旧值和新值：

-- 更新并返回变化前后
UPDATE accounts 
SET balance = balance - 100 
WHERE id = 1
RETURNING 
    id,
    OLD.balance AS old_balance,
    NEW.balance AS new_balance,
    OLD.balance - NEW.balance AS change;

-- 输出:
-- id | old_balance | new_balance | change
-- 1  | 1000.00     | 900.00      | 100.00

5.2 删除操作返回旧值

-- 删除并返回被删除的数据
DELETE FROM orders 
WHERE status = 'cancelled'
RETURNING 
    id,
    OLD.status AS deleted_status,
    OLD.customer_id AS deleted_customer;

5.3 MERGE 语句支持

-- 使用 MERGE 进行 upsert
MERGE INTO customers c
USING new_customers nc ON c.email = nc.email
WHEN MATCHED THEN
    UPDATE SET name = nc.name, updated_at = NOW()
WHEN NOT MATCHED THEN
    INSERT (email, name, created_at) VALUES (nc.email, nc.name, NOW())
RETURNING 
    id,
    MERGE_ACTION() AS action,
    OLD.email AS old_email,
    NEW.email AS new_email,
    OLD.name AS old_name,
    NEW.name AS new_name;

六、安全与认证升级

6.1 OAuth 2.0 认证支持

PostgreSQL 18 原生支持 OAuth 2.0 认证：

# pg_hba.conf 配置
hostssl all all 0.0.0.0/0 oauth issuer="https://auth.example.com" client_id="pg-client"

-- 加载 OAuth 验证库
LOAD 'oauth_validator';

-- 配置验证器
ALTER SYSTEM SET oauth_validator_libraries = 'oauth_validator';

OAuth 认证流程：

1. 客户端连接 PostgreSQL
2. PostgreSQL 返回 OAuth 授权 URL
3. 客户端完成 OAuth 认证，获取 access token
4. 客户端使用 token 连接
5. PostgreSQL 验证 token 有效性

6.2 MD5 密码弃用

-- 创建用户时使用 MD5 会收到警告
CREATE ROLE testuser WITH PASSWORD 'secret';
-- WARNING: MD5 password authentication is deprecated and will be removed in a future version

-- 推荐使用 SCRAM-SHA-256
CREATE ROLE testuser WITH PASSWORD 'secret' ENCRYPTED BY 'scram-sha-256';

-- 控制警告显示
SET md5_password_warnings = off;  -- 临时禁用警告

-- 查看当前密码加密方式
SELECT rolname, rolpassword 
FROM pg_authid 
WHERE rolname = 'testuser';
-- 输出: SCRAM-SHA-256$...

6.3 TLS 1.3 密码套件配置

-- 配置 TLS 1.3 密码套件
ALTER SYSTEM SET ssl_tls13_ciphers = 'TLS_AES_256_GCM_SHA384:TLS_CHACHA20_POLY1305_SHA256';

-- 配置 ECDH 曲线
ALTER SYSTEM SET ssl_groups = 'x25519:prime256v1:secp384r1';

-- 重载配置
SELECT pg_reload_conf();

七、监控与可观测性

7.1 VACUUM/ANALYZE 时间统计

-- 查看表的 VACUUM 统计
SELECT 
    schemaname,
    relname,
    total_vacuum_time,
    total_autovacuum_time,
    total_analyze_time,
    total_autoanalyze_time
FROM pg_stat_all_tables
WHERE schemaname = 'public'
ORDER BY total_vacuum_time DESC;

-- 输出:
-- schemaname | relname  | total_vacuum_time | total_autovacuum_time | ...
-- public     | orders   | 00:05:23          | 00:12:45              | ...
-- public     | products | 00:01:15          | 00:03:22              | ...

7.2 每后端 I/O 统计

-- 查看每个连接的 I/O 活动
SELECT 
    pid,
    usename,
    application_name,
    pg_stat_get_backend_io(pid) AS io_stats
FROM pg_stat_activity
WHERE state = 'active';

-- 展开详细信息
SELECT 
    pid,
    (pg_stat_get_backend_io(pid)).read_bytes,
    (pg_stat_get_backend_io(pid)).write_bytes
FROM pg_stat_activity
WHERE pid = pg_backend_pid();

7.3 EXPLAIN 输出增强

-- 自动包含 BUFFERS 信息
EXPLAIN (ANALYZE) SELECT * FROM orders WHERE customer_id = 100;
-- 输出包含:
-- Buffers: shared read=1234 dirtied=56 written=12

-- WAL 缓冲区统计
EXPLAIN (ANALYZE, WAL) UPDATE orders SET status = 'processed' WHERE id < 1000;
-- 输出包含:
-- WAL: records=1000 bytes=56000 full_wal_buffers=2

-- 索引查找次数
EXPLAIN (ANALYZE) SELECT * FROM orders WHERE id = 100;
-- Index Scan using orders_pkey on orders
-- Index Lookups: 1

八、升级与迁移

8.1 pg_upgrade 增强

PostgreSQL 18 的 pg_upgrade 支持保留优化器统计信息，大幅减少升级后的性能抖动：

# 从 PostgreSQL 17 升级到 18
/usr/pgsql-18/bin/pg_upgrade \
  --old-bindir /usr/pgsql-17/bin \
  --new-bindir /usr/pgsql-18/bin \
  --old-datadir /var/lib/pgsql/17/data \
  --new-datadir /var/lib/pgsql/18/data \
  --jobs 4 \
  --swap  # 新选项：使用目录交换，更快

# 升级后无需重新 ANALYZE
# 统计信息已保留

8.2 数据校验和默认启用

# initdb 现在默认启用数据校验和
initdb -D /var/lib/pgsql/18/data

# 如需禁用
initdb -D /var/lib/pgsql/18/data --no-data-checksums

8.3 升级兼容性检查

-- 检查是否有依赖即将移除功能的代码
-- 例如 MD5 认证
SELECT rolname, rolpassword 
FROM pg_authid 
WHERE rolpassword LIKE 'md5%';

-- 检查时区缩写使用
SELECT * FROM pg_timezone_abbrevs;

九、实战案例：性能优化全景

9.1 案例一：电商订单查询优化

场景：查询某客户的所有订单及其状态分布

-- 表结构
CREATE TABLE orders (
    id UUID DEFAULT uuidv7() PRIMARY KEY,
    customer_id INT NOT NULL,
    status VARCHAR(20) NOT NULL,
    amount DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT NOW()
);

-- 创建优化索引
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

-- PostgreSQL 18 优化后的查询
EXPLAIN ANALYZE
SELECT 
    status,
    COUNT(*) as order_count,
    SUM(amount) as total_amount
FROM orders
WHERE customer_id = 100
GROUP BY status;

-- 输出显示使用 Index Only Scan + Skip Scan
-- 性能提升约 40%

9.2 案例二：时序数据分析

-- 传感器数据表
CREATE TABLE sensor_readings (
    id UUID DEFAULT uuidv7() PRIMARY KEY,
    sensor_id INT NOT NULL,
    reading_value DECIMAL(10,4),
    recorded_at TIMESTAMPTZ DEFAULT NOW()
) PARTITION BY RANGE (recorded_at);

-- 创建分区
CREATE TABLE sensor_readings_2026_01 
    PARTITION OF sensor_readings 
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

-- 使用异步 I/O 优化大规模扫描
SET io_combine_limit = '512kB';

-- 分析查询
EXPLAIN ANALYZE
SELECT 
    sensor_id,
    date_trunc('hour', recorded_at) AS hour,
    AVG(reading_value) AS avg_value,
    MAX(reading_value) - MIN(reading_value) AS range
FROM sensor_readings
WHERE recorded_at BETWEEN '2026-01-01' AND '2026-01-31'
GROUP BY sensor_id, date_trunc('hour', recorded_at);

-- PostgreSQL 18 使用异步 I/O 进行并行扫描
-- 性能提升约 2.5 倍

9.3 案例三：全文搜索优化

-- 文档表
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    search_vector tsvector GENERATED ALWAYS AS (
        to_tsvector('english', coalesce(title, '') || ' ' || coalesce(content, ''))
    ) STORED
);

-- 创建 GIN 索引
SET max_parallel_maintenance_workers = 4;
CREATE INDEX idx_articles_search ON articles USING gin(search_vector);

-- 全文搜索
EXPLAIN ANALYZE
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('postgresql & performance') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

-- PostgreSQL 18 的优化：
-- 1. 并行 GIN 索引创建
-- 2. 虚拟生成列减少存储
-- 3. 改进的查询计划

十、总结与展望

PostgreSQL 18 是一次真正意义上的重大版本升级，其核心亮点包括：

异步 I/O 子系统：将存储读取性能提升高达 3 倍，是近年来最重要的架构革新
查询优化器增强：自连接消除、OR 转数组、DISTINCT 重排序等优化，让查询更智能
索引能力扩展：B-Tree Skip Scan、并行 GIN 创建，提升索引效率
数据类型升级：UUIDv7、虚拟生成列、新增数组函数，更贴近实际需求
安全与认证：OAuth 2.0 支持、MD5 弃用、TLS 1.3 增强
监控可观测性：更详细的 I/O 统计、VACUUM 时间追踪、EXPLAIN 输出增强

对于已经在使用 PostgreSQL 的团队，升级到 18 的主要收益点：

I/O 密集型场景：异步 I/O 带来显著性能提升
大规模数据扫描：顺序扫描、位图扫描优化明显
索引创建：并行化大幅缩短维护窗口
UUID 主键：UUIDv7 解决索引碎片问题

PostgreSQL 的发展方向清晰可见：在保持 SQL 兼容性和数据安全的同时，不断突破性能边界。异步 I/O 的引入，标志着 PostgreSQL 正在从传统的同步架构向现代异步架构演进。对于开发者而言，理解并善用这些新特性，将在未来的数据库应用开发中获得显著优势。

升级建议：

生产环境建议等待 18.1 或 18.2 版本稳定后升级
升级前在测试环境验证兼容性
关注 MD5 认证弃用，提前迁移到 SCRAM
利用 pg_upgrade --swap 加速升级过程

PostgreSQL 18 的发布，再次证明了开源数据库的创新活力。从 1986 年 POSTGRES 项目启动至今，近 40 年的发展，PostgreSQL 始终保持着对技术卓越的追求。异步 I/O 这一底层架构的重构，为未来的性能优化奠定了坚实基础。我们有理由相信，PostgreSQL 将继续引领开源关系型数据库的技术方向。

复制全文生成海报 PostgreSQL 数据库异步I/O 性能优化 UUIDv7