postgres-tuning

PostgreSQL 调优

概述

优化 PostgreSQL 17/18+ 在 I/O、查询执行、索引和维护方面的性能。涵盖 PostgreSQL 18 引入的原生 AIO 子系统，可提升现代存储设备的吞吐量；使用 EXPLAIN BUFFERS 进行查询计划取证分析（PG18 中会自动包含）；复合索引的 B-tree 跳跃扫描；原生 UUIDv7 生成；以及针对高频变更表的 autovacuum 调优。

**适用场景：**诊断慢查询、配置异步 I/O、调优 shared_buffers 和 work_mem、为写入密集型工作负载优化索引、管理表膨胀、调优 pgvector HNSW。

**不适用场景：**模式设计（请使用数据建模工具）、应用层缓存策略、数据库选型决策、ORM 查询生成。

关键监控视图：

• pg_stat_statements — 根据累计执行时间识别慢查询模式
• pg_stat_io — 按后端类型、对象和上下文进行细粒度 I/O 分析（PG16+）
• pg_stat_checkpointer — 检查点频率和时间（PG17+；此前位于 pg_stat_bgwriter 中）
• pg_stat_user_tables — 用于检测表膨胀和监控 autovacuum 的死元组计数
• pg_statio_user_tables — 每个表的缓冲区缓存命中率
• pg_aios — 正在进行的 AIO 操作（PG18+）

快速参考

模式	配置 / 查询	要点
异步 I/O	io_method = worker or io_uring	PG18 默认值为 worker；io_uring 仅限 Linux（内核 5.1+，需要启用 liburing 构建标志）
I/O 并发	io_max_concurrency and io_workers	io_workers 默认为 3；io_max_concurrency 默认为 -1（自动计算）
取证式 EXPLAIN	EXPLAIN (ANALYZE, BUFFERS, SETTINGS)	PG18 在使用 ANALYZE 时会自动包含 BUFFERS；目标是 Shared Hit > 95%
UUIDv7 主键	DEFAULT uuidv7()	PG18 内置；按时间排序，在单个会话内单调递增；符合 RFC 9562
B-tree 跳跃扫描	在 (a, b) 上创建复合索引	PG18 会跳过前导列；前缀基数较低且尾部列使用等值条件时效果最佳
激进的 autovacuum	autovacuum_vacuum_scale_factor = 0.01	行变更达到 1% 时触发，而非默认的 20%
Shared buffers	从 RAM 的 25% 开始	未经基准测试，不要超过 40%
work_mem 调优	每个会话执行 SET work_mem = '64MB'	防止排序溢写到磁盘；按操作符分配，而非按查询分配
BRIN 索引	CREATE INDEX USING brin(...)	对物理顺序排列的时间序列数据，比 B-tree 小 100 倍
HNSW 向量索引	USING hnsw (col vector_cosine_ops)	调整 m（默认值为 16）和 ef_construction（默认值为 64），以平衡召回率与速度
GIN 索引	CREATE INDEX USING gin(...)	JSONB 包含查询、全文搜索、数组运算符；写入较慢
检查点调优	checkpoint_timeout = 30min	将写入分散到超时时间窗口的 90%，避免 I/O 风暴
WAL 压缩	wal_compression = zstd	PG15 起提供；对于写入密集型工作负载，可减少 50–70% 的 WAL I/O
膨胀检测	pg_stat_user_tables.n_dead_tup	如果膨胀率 > 30%，则并发重建索引
I/O 监控	SELECT * FROM pg_stat_io	关注 evictions（缓存过小）和 extends（快速增长）
检查点监控	pg_stat_checkpointer	PG17+ 将检查点统计信息从 pg_stat_bgwriter 移出

关键版本变更

PostgreSQL 18：

• 通过 io_method 参数提供原生异步 I/O（仅适用于读取；写入仍为同步）
• 内置 uuidv7() 函数，可在会话内保证单调排序（RFC 9562）
• uuidv4() 是 gen_random_uuid() 的别名，uuid_extract_timestamp() 可用于 UUIDv7
• B-tree 复合索引支持跳跃扫描（尾部列使用等值条件，前缀列基数较低）
• EXPLAIN ANALYZE 会自动包含缓冲区统计信息，无需指定 BUFFERS
• pg_stat_io 新增字节级列（read_bytes、write_bytes、extend_bytes）；移除 op_bytes
• effective_io_concurrency 的默认值从 1 改为 16
• 通过 pg_aios 系统视图监控正在进行的 I/O 操作

PostgreSQL 17：

• 检查点统计信息从 pg_stat_bgwriter 移至 pg_stat_checkpointer
• 列重命名：checkpoints_timed 改为 num_timed，checkpoints_req 改为 num_requested
• 从 pg_stat_bgwriter 中移除 buffers_backend 和 buffers_backend_fsync（现位于 pg_stat_io 中）

PostgreSQL 15：

• wal_compression 从布尔值扩展为支持 pglz、lz4 和 zstd 算法

常见错误

错误	正确做法
使用 uuid_generate_v7() 或 gen_random_uuid() 生成有序键	PG18 提供内置的 uuidv7() 生成按时间排序的 UUID；PG18 之前的版本使用 pg_uuidv7 扩展
将 max_async_ios 用作配置参数	PG18 的正确参数是 io_max_concurrency（每个进程可并发执行的最大 I/O 操作数）
在 PG17+ 上查询 pg_stat_bgwriter 获取检查点统计信息	PG17 中检查点统计信息已移至 pg_stat_checkpointer；列也已重命名（num_timed、num_requested）
在高频查询中使用 SELECT *	仅选择所需列，以减少 I/O 并提高缓存命中率
忽略超过 10k 行的表上的顺序扫描	为 WHERE、ORDER BY 和 JOIN 子句中使用的列添加有针对性的索引
未经测试就将 shared_buffers 设为超过 RAM 的 40%	从 25% 开始并进行基准测试；分配过多会造成操作系统页缓存争用
对频繁变更的表保留 autovacuum 默认设置	对频繁执行 UPDATE/DELETE 的表，将 autovacuum_vacuum_scale_factor 调整为 0.01
为查询中很少使用的列创建过多索引	每增加一个索引都会降低 UPDATE/INSERT 的速度，并阻止 HOT（仅堆元组）更新
期望 B-tree 跳跃扫描适用于范围条件	PG18 跳跃扫描仅适用于尾部列上的等值运算符
忽略查询计划中的 "External Merge Disk"	为特定会话增大 work_mem；这表示排序数据溢写到了磁盘
未确认构建标志就设置 io_method = io_uring	PostgreSQL 必须使用 --with-liburing 构建，且需要 Linux 内核 5.1+
认为 PG18 AIO 能加速写入	PG18 中的 AIO 仅涵盖读取操作（顺序扫描、位图堆扫描、VACUUM）；写入仍为同步

调优工作流

1. 识别 pg_stat_statements 中的慢查询（按 total_exec_time 排序）
2. 使用 EXPLAIN (ANALYZE, BUFFERS, SETTINGS) 分析执行计划
3. 通过 pg_statio_user_tables 检查缓冲区命中率（目标 > 99%）
4. 通过 pg_stat_io 监控 I/O 模式（关注驱逐和磁盘读取）
5. 使用针对性索引、调整 work_mem 或重写查询进行优化
6. 重新运行 EXPLAIN 并比较成本，验证优化效果
7. 对于高变更率的表，通过激进的 autovacuum 设置进行维护

委派

• 发现慢查询和 I/O 瓶颈：使用 Explore Agent 分析 pg_stat_statements、pg_stat_io 和慢查询日志
• 执行查询计划分析和索引优化：使用 Task Agent 运行 EXPLAIN ANALYZE、创建索引并验证性能改进
• 设计数据库扩展和分区策略：使用 Plan Agent 规划分片、分区和复制拓扑

参考资料

• 异步 I/O 配置和存储调优
• 查询计划分析和算子取证
• 索引策略和膨胀管理
• 连接池、分区和查询模式