PostgreSQLNotes

第 26 章：性能调优

zjc 于 2026-01-26 发布

这是《PostgreSQL 零基础实战指南》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。 性能调优是系统化工程：先定义目标和基线，再定位瓶颈，最后通过压测验证配置和 SQL 改动。

26.1 调优流程

确定 SLO
  -> 建立基线
  -> 采集资源和 SQL
  -> 定位瓶颈
  -> 一次改一个变量
  -> 压测
  -> 固化配置和规范

26.2 参数分层

层 参数
连接 max_connections、superuser_reserved_connections
内存 shared_buffers、work_mem、maintenance_work_mem
WAL max_wal_size、checkpoint_timeout、synchronous_commit
查询 default_statistics_target、random_page_cost
自动清理 autovacuum_*
日志 log_min_duration_statement

26.3 连接池

连接不是越多越好。应用应使用 PgBouncer 或应用内连接池。

max_connections = 200
PgBouncer pool = 50-100，按业务拆池
事务级或会话级模式按语义选择

事务级 pooling 不支持依赖连接状态的特性，如 prepared statement 的部分行为、SET SESSION、 advisory lock 语义。

26.4 内存参数

ALTER SYSTEM SET shared_buffers = '4GB';
ALTER SYSTEM SET effective_cache_size = '12GB';
ALTER SYSTEM SET work_mem = '32MB';
ALTER SYSTEM SET maintenance_work_mem = '512MB';
SELECT pg_reload_conf();

ALTER SYSTEM 写入自动配置文件，仍要纳入版本管理。

26.5 IO 调优

关注：

  1. 数据盘类型；
  2. WAL 与数据是否分离；
  3. full_page_writes；
  4. 检查点分布；
  5. bgwriter；
  6. 表和索引布局；
  7. 临时文件位置。

查看 IO 等待：

SELECT wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE state <> 'idle'
GROUP BY 1, 2;

26.6 SQL 调优

优先级：

  1. 修复缺索引；
  2. 修复统计信息；
  3. 减少扫描列和行；
  4. 优化排序聚合；
  5. 控制返回结果；
  6. 消除 N+1；
  7. 使用绑定参数；
  8. 缓存稳定结果。

26.7 批量作业

大作业分批：

WITH batch AS (
    SELECT id
    FROM tasks
    WHERE status = 'NEW'
    ORDER BY id
    LIMIT 1000
    FOR UPDATE SKIP LOCKED
)
UPDATE tasks t
SET status = 'RUNNING'
FROM batch
WHERE t.id = batch.id
RETURNING t.id;

26.8 基准测试

pgbench -i -s 10 testdb
pgbench -c 20 -j 4 -T 120 testdb

测试前记录：

  1. 数据规模；
  2. 参数；
  3. 硬件；
  4. 查询集；
  5. 并发模型；
  6. 系统指标。

本章小结

性能调优不能脱离负载模型。先治理 SQL 和索引，再谨慎调整内存、WAL 和 autovacuum，并用连接池和压测验证并发能力。

思考题

  1. 为什么 max_connections 不是越大越好？
  2. 事务级连接池有什么限制？
  3. work_mem 调大的风险是什么？
  4. 批量任务为什么分批提交？
  5. 如何建立可信压测基线？