DBA夜读·第八季第11期｜性能压测与对比——pgbench实战、AIO效果验证、Skip Scan测试

第八季·《PostgreSQL 18 新特性深度解析》 本季将聚焦PostgreSQL 18这一里程碑版本，系统性地探索从异步I/O到增量备份、从VACUUM革命到开发者体验升级的全方位新特性。本季旨在帮助DBA和开发者快速掌握PostgreSQL 18的核心变化，为生产环境升级和新项目选型提供决策依据。

【上期回顾】

上一期我们深入解析了PostgreSQL 18的升级实战指南：

• • 重大兼容性变更：数据校验和默认启用、MD5弃用、VACUUM默认处理继承表
• • pg_upgrade增强：保留优化器统计信息、--jobs并行检查、--swap模式
• • 回滚策略：link模式不可回滚、swap模式破坏旧集群、备份是回滚的前提
• • 升级后验证：性能基准对比、统计信息检查、扩展兼容性验证

掌握了升级实战，你就能安全地将生产环境迁移到PostgreSQL 18。而本期我们将聚焦性能压测与对比——通过pgbench等工具，验证PostgreSQL 18的各项性能改进。

【本集概览】

PostgreSQL 18引入了异步I/O（AIO）、Skip Scan、并行GIN索引构建等多项性能特性。本章将通过pgbench压测、TPC-H基准测试等方式，全面验证这些新特性的实际效果。

【第一部分】pgbench压测工具详解

1.1 pgbench概述

pgbench是PostgreSQL自带的基准测试工具，也是社区最常用的性能验证手段。它通过模拟多个并发客户端执行SQL事务，计算数据库的TPS（每秒事务数）和延迟表现。

根据PostgreSQL 18.2官方文档，pgbench的核心定位是"对PostgreSQL运行基准测试的简单程序"。它默认执行一个基于TPC-B的测试场景，每个事务包含5个SELECT、UPDATE和INSERT命令。

# pgbench输出示例
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10
number of clients: 10
number of transactions actually processed: 10000/10000
latency average = 11.013 ms
tps = 896.967014 (without initial connection time)

1.2 初始化选项

在使用pgbench进行压测之前，需要先初始化测试表。关键初始化选项如下：

1.3 压测选项

初始化完成后，使用以下选项执行压测：

1.4 结果解读

根据官方文档，pgbench输出中以下指标需要重点关注：

1.5 自定义脚本示例

-- custom.sql：模拟电商下单场景
\set user_id random(1, 100000)
\set product_id random(1, 10000)
\set amount random(10, 1000)

BEGIN;
    SELECT * FROM users WHERE id = :user_id;
    UPDATE products SET stock = stock - 1 WHERE id = :product_id AND stock > 0;
    INSERT INTO orders (user_id, product_id, amount, created_at)
    VALUES (:user_id, :product_id, :amount, now());
COMMIT;

# 使用自定义脚本压测
pgbench -c 50 -T 300 -f custom.sql -M prepared -j 4 mydb

【第二部分】AIO性能压测

2.1 AIO配置参数回顾

根据IvorySQL社区的技术分析，PostgreSQL 18的异步I/O通过以下GUC参数控制：

AIO框架的设计哲学是"解耦I/O请求的发起和完成"。在同步I/O模式下，每个后端进程发出磁盘请求后必须等待操作系统返回结果；而在异步I/O模式下，数据库可以继续执行查询计划的其他部分，数据就绪后再回来处理。

2.2 环境要求

io_uring模式的特殊要求：

• • 需要Linux内核5.1+（推荐5.10+）
• • PostgreSQL需使用--with-liburing
  编译
• • 某些云数据库环境（如AWS RDS）仅支持sync
  和worker
  模式，不支持io_uring
  

2.3 压测方案设计

测试环境建议：

• • 实例规格：4C16G以上（生产级测试）
• • 存储：NVMe SSD或高IOPS云盘（如AWS gp3 12000 IOPS）
• • 数据集大小：大于内存（确保真实I/O）

对比测试方案：

根据Cybrosys的技术文档，推荐的AIO压测方案如下：

-- 1. 创建测试表（确保数据量大于shared_buffers）
CREATE TABLE aio_test (
    id SERIAL PRIMARY KEY,
    data TEXT,
    value INTEGER
);

INSERT INTO aio_test (data, value)
SELECT md5(random()::text), (random() * 1000000)::INTEGER
FROM generate_series(1, 10000000);  -- 1000万行

-- 2. 清空OS缓存（确保测试从磁盘读取）
CHECKPOINT;
-- 重启PostgreSQL或使用pg_prewarm清空缓存

-- 3. sync模式测试
ALTER SYSTEM SET io_method = 'sync';
-- 重启数据库
EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(*) FROM aio_test;

-- 4. worker模式测试
ALTER SYSTEM SET io_method = 'worker';
-- 重启数据库
EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(*) FROM aio_test;

2.4 官方社区测试结果

顺序扫描性能提升：

根据IvorySQL社区的早期测试，异步I/O在顺序扫描场景下表现突出：

根据官方消息，在某些存储密集型场景下，性能提升可高达3倍。

RDS环境测试结果：

来自开发者社区对AWS RDS PostgreSQL 18的实测数据显示：

测试环境：db.m6g.large（2 vCPU，8GB RAM），EBS gp3（400GB，12,000 IOPS）。

结果分析：

• • COUNT(*)这类全表扫描受益明显（-16%）
• • pgbench混合读写负载提升有限（约1%）
• • 部分聚合查询反而出现性能下降，可能与AIO当前仅支持异步读有关

💡 重要发现：在RDS环境中，io_method
仅支持sync
和worker
，不支持io_uring
。这也是worker模式提升有限的原因之一——核心的性能飞跃预期来自io_uring
后端。

2.5 io_workers调优陷阱

盲目增加io_workers
可能适得其反。测试显示，在2 vCPU、gp2（120 IOPS基线）的环境下，将io_workers
从3增加到32后：

原因分析：

• • 过多的worker同时访问存储造成I/O队列拥堵
• • 存储性能基线（120 IOPS）成为瓶颈
• • 大量worker反而增加了调度开销

调优建议：io_workers
应配合实例资源设置。在2 vCPU环境中，默认的3个worker已经足够。

2.6 真实场景测试：IN子句性能回归

性能测试不仅是验证新特性的收益，也要警惕潜在的回归。根据pgsql-bugs邮件列表的报告，PostgreSQL 18在处理大IN子句时存在性能回退：

解决方案：将IN
子句改写为JOIN UNNEST
模式，性能可恢复甚至超越旧版本。

这一发现提醒我们：在升级前应对核心查询进行充分的性能基准测试。

【第三部分】Skip Scan效果验证

3.1 Skip Scan原理

在多列B-tree索引中，如果查询条件跳过了前导列，传统优化器无法使用该索引，只能选择全表扫描。PostgreSQL 18引入了跳跃扫描（Skip Scan）机制：优化器在遍历索引时，动态生成前导列的每个可能值作为等式约束，将一次扫描拆分为多次跳跃扫描。

IvorySQL社区的测试直观展示了这一差异：

-- 创建复合索引
CREATE INDEX idx_t1_c1c2 ON t1(c1, c2);

-- 查询使用第二列条件（跳过前导列）
SELECT * FROM t1 WHERE c2 = 100;

3.2 性能对比测试

根据IvorySQL社区的测试数据：

测试环境：

• • 表：100万行
• • 索引：idx_t1_c1c2 ON t1(c1, c2)
  
• • 查询：SELECT * FROM t1 WHERE c2=100;
  

PostgreSQL 17执行计划（使用并行顺序扫描）：

PostgreSQL 18执行计划（使用Skip Scan）：

适用条件：

• • 前导列的不同值数量较少时效果最佳
• • 前导列不同值较多时，跳跃次数增加，效果递减

【第四部分】并行VACUUM测试

4.1 语法与限制

PostgreSQL 18的VACUUM新增PARALLEL
选项：

-- 使用4个并行工作进程
VACUUM (PARALLEL 4) large_table;

-- 查看并行VACUUM进度
SELECT * FROM pg_stat_progress_vacuum;

限制条件：

• • 每个索引最多使用一个工作进程
• • 只有当表至少有2个索引时才会启动并行
• • 不能与FULL
  选项一起使用

4.2 并行VACUUM测试方案

-- 1. 创建多索引的大表
CREATE TABLE vacuum_test (
    id SERIAL PRIMARY KEY,
    col1 INT,
    col2 VARCHAR(100),
    col3 TIMESTAMP,
    data TEXT
);

CREATE INDEX idx_vac_test_col1 ON vacuum_test(col1);
CREATE INDEX idx_vac_test_col2 ON vacuum_test(col2);
CREATE INDEX idx_vac_test_col3 ON vacuum_test(col3);

-- 2. 插入大量数据
INSERT INTO vacuum_test (col1, col2, col3, data)
SELECT i, md5(i::text), now(), repeat('x', 100)
FROM generate_series(1, 5000000) i;

-- 3. 模拟大量更新产生死元组
UPDATE vacuum_test SET col1 = col1 + 1 WHERE id % 10 = 0;

-- 4. 对比串行vs并行VACUUM
VACUUM VERBOSE vacuum_test;                    -- 串行
VACUUM (PARALLEL 4) VERBOSE vacuum_test;       -- 并行

4.3 预期效果

在多核服务器上，并行VACUUM可显著缩短清理时间，特别是对于拥有大量索引的表。根据社区反馈，索引较多的表受益最为明显。

【第五部分】硬件配置与参数调优建议

5.1 存储类型选择

5.2 AIO参数调优模板

NVMe SSD + Linux 5.10+（高性能配置） ：

# postgresql.conf
io_method = io_uring
effective_io_concurrency = 300
maintenance_io_concurrency = 300
io_combine_limit = 32
io_max_concurrency = 128

通用SSD配置 ：

io_method = worker
io_workers = 3
effective_io_concurrency = 16
maintenance_io_concurrency = 16
io_combine_limit = 16
io_max_concurrency = -1

RDS等云数据库环境：

io_method = worker          # io_uring不可用
effective_io_concurrency = 32
maintenance_io_concurrency = 32

5.3 内存参数调优

根据PostgreSQL 18的最佳实践：

5.4 监控AIO状态

通过pg_aios
视图实时监控异步I/O执行状况：

-- 查看当前AIO句柄状态
SELECT pid, state, operation, length, target_desc 
FROM pg_aios 
WHERE state NOT IN ('COMPLETED_LOCAL', 'COMPLETED_SHARED');

-- 统计各状态I/O数量
SELECT state, COUNT(*) 
FROM pg_aios 
GROUP BY state;

【第六部分】本章总结

6.1 核心性能指标速查

6.2 压测命令速查

# 1. 初始化测试数据（1亿行，约15GB）
pgbench -i -s 1000 -h <host> -U <user> -d postgres

# 2. TPC-B压测（20并发，5分钟）
pgbench -c 20 -j 4 -T 300 -h <host> -U <user> -d postgres

# 3. 自定义脚本压测
pgbench -c 50 -T 300 -f custom.sql -M prepared -j 4 mydb

# 4. 全表扫描性能测试
psql -h <host> -U <user> -d postgres -c "\timing on" -c "SELECT COUNT(*) FROM pgbench_accounts;"

6.3 性能基线建议

升级前应建立性能基线：

6.4 下期预告

第12期：生产环境部署指南——配置模板、最佳实践、常见问题

我们将深入PostgreSQL 18的生产环境部署：

• • 配置模板：不同规模生产环境的postgresql.conf模板
• • 迁移策略：从PG 16/17迁移到18的最佳路径
• • 扩展兼容性：常用扩展的PG 18兼容性状态
• • 常见问题：升级后的常见问题排查
• • 监控配置：生产环境监控项配置

生产部署是DBA的核心职责，下期见！

本文为学习笔记，内容基于PostgreSQL 18官方文档、pgbench手册、IvorySQL社区技术分析及社区测试数据提炼总结。