PostgreSQL 性能调优检查清单听起来像一张简单的事项列表但实际上它是我每次接手新库、处理慢查询、或者大版本升级前都会完整过一遍的体检流程。数据库调优这件事最怕的不是问题复杂而是毫无章法地东改一个参数、西加一个索引最后性能没上去反而把系统弄得比以前更不稳定。这篇文章就是把我平时用的这套检查清单完整拆开从内存参数、查询计划、维护策略到监控验证每一块都讲清楚为什么要这么调、具体怎么落地。我不打算只给结论因为这行真正的价值在于判断依据和操作细节。比如 shared_buffers 到底设多大不是拍脑袋定 25% 就行得结合机器的总内存、连接数、以及 workload 类型一起算work_mem 也不是越大越好改错了会造成内存超卖直接拖垮系统。这些账我都会一步步算给你看。无论你是刚接手 PostgreSQL 的运维、正在为业务慢查询焦头烂额的开发者还是准备做一次系统性优化的 DBA按这份清单走一遍你至少能知道瓶颈在哪个环节以及下一步该动哪里。1. 建基线、找瓶颈、再动手调优的正确顺序1.1 先用十分钟建立一套可对比的性能基线很多朋友调优失败不是因为参数改错而是因为没有基线。没有基线意味着你根本不知道优化前后到底提升了多少甚至某个改动让系统变差了你自己都感觉不出来。所以我每次动手前第一件事就是花十分钟把系统的当前状态记录下来。一套简单有效的基线至少包含这几类数据当前负载情况CPU、内存、磁盘 IO、网络流量用 top、iostat、sar 或者云监控都能看。PostgreSQL 关键视图快照pg_stat_database、pg_stat_user_tables、pg_stat_bgwriter、pg_stat_activity。慢查询情况pg_stat_statements 里 Top N 的耗时统计以及日志里的慢 SQL。连接的分布活跃连接数、空闲连接数、等待事件类型。其中 pg_stat_statements 是我最依赖的视图。如果没有开启你需要先改配置-- postgresql.conf shared_preload_libraries pg_stat_statements track_io_timing on改完重启实例后创建扩展CREATE EXTENSION IF NOT EXISTS pg_stat_statements;之后用这条 SQL 就能快速定位最耗时的查询SELECT query, calls, total_exec_time / 1000 AS total_ms, mean_exec_time / 1000 AS mean_ms, rows / calls AS avg_rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;注意不同版本的时间字段名不太一样旧版本可能是 total_time、mean_time从 PG14 左右开始才统一成 exec_time 系列。以你当前实例的字段为准就行。建立基线以后所有参数调整和索引变更就都有了参照系。1.2 不要上来就改 shared_buffers瓶颈优先而非参数优先我见过太多人拿到一台数据库服务器第一反应就是“先加内存参数”。这其实是一种本末倒置的做法。参数只是放大器如果 SQL 本身就是全表扫描、锁等待或者磁盘瓶颈你把 shared_buffers 调到 80% 也不会让业务变快。所以正确的顺序是先通过等待事件和慢查询确认瓶颈方向再针对性地改配置。PostgreSQL 的等待事件大致能告诉我们瓶颈在哪一层等待事件类型常见含义优先排查方向ClientRead/ClientWrite客户端读写慢或者 SQL 本身复杂SQL 执行计划、网络延迟DataFileRead / DataFileWrite数据文件 IO 慢磁盘负载、shared_buffers、索引质量WALWrite / WALWriteLockWAL 写入瓶颈磁盘 fsync 性能、wal 参数Lock / Transaction锁等待或长事务锁冲突、事务提交频率CPU 相关等待计算密集SQL 逻辑、并发模型举个例子如果你在 pg_stat_activity 里看到大量会话卡在 DataFileRead说明数据库确实在频繁读取磁盘这时加大 shared_buffers 或调整 effective_cache_size 是合理的。但如果大量会话都卡在 ClientRead这意味着 SQL 执行本身很快反而是应用端或网络拖了后腿你改 PostgreSQL 参数毫无意义。先定位再动手这是整个检查清单的底层逻辑。2. 实例级配置先把内存和写入参数算清楚2.1 shared_buffers 与 effective_cache_size一个管命中一个管计划shared_buffers 是 PostgreSQL 自己的共享缓冲池它决定多少数据页可以缓存在内存里。常见的建议是设置为物理内存的 25% 左右。这个比例不是一个精确公式但它考虑了 PostgreSQL 的缓存管理机制和操作系统页缓存的分工。以一个 64GB 内存的专用数据库服务器为例shared_buffers 64GB * 25% 16GBeffective_cache_size 64GB * 75% 48GB为什么不直接给到 40GB因为除了 shared_buffers系统还要留下内存给操作系统文件缓存、连接会话的私有内存、WAL、以及日常管理进程。shared_buffers 过大反而可能导致操作系统无法有效缓存数据文件实际效果适得其反。我给一些客户调优时发现shared_buffers 从 4GB 提升到 16GB 后物理读确实明显下降但一旦超过 50% 总内存性能反而出现抖动原因就是操作系统的页面缓存被挤占太严重。effective_cache_size 不会实际分配内存它只是用来告诉查询优化器“系统总共有多少可用的页面缓存”。这个值设置得越接近操作系统实际可用的缓存总量优化器对索引扫描和排序成本的估算就越准确。如果这个值设得太低优化器会倾向于选择顺序扫描低估索引的价值设得过高则反过来。修改后记得用 ALTER SYSTEM 或直接改 postgresql.conf并 reloadALTER SYSTEM SET shared_buffers 16GB; ALTER SYSTEM SET effective_cache_size 48GB;这个操作不需要重启但 shared_buffers 其实是需要重启才能完全生效的参数ALTER SYSTEM 只是把配置写入文件得重启实例才会真正分配。effective_cache_size 可以 reload 后生效。2.2 work_mem 与 max_connections内存超卖的账必须算清楚work_mem 是每个排序、哈希连接、聚合操作可以使用的内存。它是按操作分配的而不是按会话固定分配这个细节多数人都清楚但实际规划时还是会犯错。假设你把 work_mem 设置为 64MB一台服务器有 200 个连接同时有 100 个会话在跑排序操作那么潜在内存占用就是 64MB * 100 6.4GB。如果再碰上嵌套循环里的排序操作一个查询可能同时消耗多个 work_mem 配额内存超卖就更严重了。所以我的经验是默认的 4MB 对于大部分 OLTP 场景是合适的需要调大的只是那些“特定的大查询”。你可以在会话级别临时调高而不是一改就是全局SET work_mem 256MB; -- 执行你的大查询 RESET work_mem;从全局参数的角度可以用这个公式来估算安全上限可用内存 总内存 - shared_buffers - 系统及其他进程预留 work_mem 上限 ≈ 可用内存 / (max_connections * 可能的并发操作系数)比如 64GB 内存shared_buffers 16GB系统预留 8GB可用内存约 40GBmax_connections 200那么全局 work_mem 即使按 10MB 算最坏情况也要 2GB 以上勉强安全但如果某个连接内部又跑多个排序操作还是会爆。所以我的原则是全局保持低水位查询级按需增加。maintenance_work_mem 则是 VACUUM、CREATE INDEX 这类维护操作使用的内存可以独立调大。它不受连接数影响所以大表建索引或手动 VACUUM 时给到 1GB 甚至 2GB 都是安全的。2.3 checkpoint 与 WAL磁盘抖动和恢复时间的真正影响因素很多人把调优重心放在缓存和查询上忽视了 checkpoint 参数。你会发现系统平时很平稳但每隔一段时间磁盘 IO 就像被捅了一刀CPU wa 飙升查询全部变慢。这一般就是 checkpoint 集中刷脏页导致的。PostgreSQL 默认的 checkpoint_timeout 是 5 分钟max_wal_size 是 1GB。如果数据库写入量大默认值会导致 checkpoint 频繁触发每次都要把大量脏页刷到磁盘形成 IO 尖刺。我通常会将这两个参数配合调整checkpoint_timeout 10min 或 15minmax_wal_size 4GB 到 8GB 甚至更高取决于磁盘空间和恢复时间要求min_wal_size 1GB 左右为什么 max_wal_size 不能一味的调大因为它直接影响崩溃恢复时间。如果数据库崩溃PostgreSQL 需要重放从上一次 checkpoint 以来的所有 WAL 日志WAL 越多恢复时间就越长。所以在 SSD 和磁盘空间充裕的前提下可以适当调大但必须结合你业务对 RTO 的要求。另外还有两个容易被忽略的参数。一个是 wal_buffers默认值对于高并发写入场景偏小通常可以直接设置为 16MB 到 64MB 之间。另一个是 synchronous_commit如果业务能接受少部分事务在崩溃时丢失可以设置为 off换取更高的吞吐量。这个选项需要应用层配合不能拍脑袋就关。3. 查询层定位慢 SQL 和把索引建在刀刃上3.1 用 pg_stat_statements 先锁定“贡献最大”的 SQL性能瓶颈通常不是平均分布而是集中在少数几条高频 SQL 上。所以我不会随手去翻慢查询日志而是先用 pg_stat_statements 按总耗时排序。total_exec_time 大可能是单次执行慢也可能是执行次数极多两者对应的优化策略完全不同。比如一条 SQL 平均 500ms但每天只跑几十次优化它的收益其实有限另一条 SQL 平均 10ms但每秒执行 100 次那它的总耗时非常高优化它收益立竿见影。所以要同时看 calls、total_exec_time、mean_exec_time、rows四个指标缺一不可。定位到目标 SQL 后用 EXPLAIN 看执行计划。记住一个原则先看有没有顺序扫描再看有没有嵌套循环失算最后看预估行数和实际行数偏差大不大。EXPLAIN (ANALYZE, BUFFERS) SELECT user_id, order_id, amount FROM orders WHERE created_at 2025-01-01 AND status paid;输出里如果出现 Seq Scan而且过滤后返回的行数占比不高说明索引缺失或者统计信息陈旧。Buffers 部分会告诉你命中了多少共享缓存、读了多少磁盘块。如果 shared hit 很少而 read 很多就算加了索引物理 IO 依然会偏高这时得从缓存和内存角度想办法。3.2 索引选型默认 B-Tree 之外覆盖索引和条件索引能救大命给 WHERE 条件加 B-Tree 索引是一个基础操作但实际生产里经常遇到“明明加了索引性能还是不行”的情况。大多数时候是因为查询需要回表或者索引选择性太差。有一个很好用的功能是覆盖索引PostgreSQL 从 11 版本开始支持 INCLUDE 子句。比如查询语句是SELECT user_id, status, created_at FROM orders WHERE user_id 123;如果只在 user_id 上建普通索引查询命中索引后还要回表取 status 和 created_at。如果建一个 INCLUDE 索引这两个字段直接从索引里拿省掉一次回表CREATE INDEX idx_orders_user_inc ON orders (user_id) INCLUDE (status, created_at);不过 INCLUDE 字段不能用于约束或排序优化它只是把数据冗余进索引换取访问速度所以列表里不要放太宽。对于高频过滤条件比如只查 status active 的数据可以考虑部分索引CREATE INDEX idx_users_active ON users (id) WHERE status active;这能让索引体积大幅缩小维护成本也更低。还有一个非常实用的类型是表达式索引比如查询经常对 email 做 lower() 匹配时直接建CREATE INDEX idx_users_lower_email ON users (lower(email));这样查询条件写成 lower(email) xxxexample.com 才能用上索引条件里不带函数的话索引是不会生效的。3.3 统计信息陈旧和参数化带来的隐藏问题执行计划选错很多时候不是索引问题而是优化器拿到的统计信息过旧。PostgreSQL 的默认 autovacuum 会负责 ANALYZE但如果表数据被大量批量修改统计信息可能来不及更新。这时即使有索引优化器也可能基于陈旧的选择性估算选择顺序扫描。遇到这种场景我一般会先执行ANALYZE orders;然后再看一眼执行计划。这一步的成本低、速度快但往往能解决很多“莫名奇怪”的慢查询。还有一个默认参数值得检查plan_cache_mode 和 prepared statements。对于大量使用 WHERE 条件变化比较大的场景每条 SQL 第一次执行都会生成一次计划解析开销会累积。可以考虑通过应用层的预处理语句让 PostgreSQL 复用执行计划。但是也要小心绑定变量会让优化器无法使用某些特殊参数值做优化特别是数据分布极不均匀时固定计划反而更慢。这类场景就需要用 plan_cache_mode force_custom_plan 单独调。4. 维护层VACUUM 和膨胀问题往往是慢查询的隐形推手4.1 autovacuum 不是开着就万事大吉PostgreSQL 的 MVCC 机制决定了删除和更新不会立即清理旧版本而是留下死元组由 VACUUM 负责清理。如果 autovacuum 跟不上数据更新速度表会迅速膨胀索引失效查询性能急剧下降甚至引发事务 ID 回卷的严重故障。很多库默认的 autovacuum 参数在生产压力下是不够的。以默认的 autovacuum_vacuum_scale_factor 0.2 为例一张 1 亿行的表要死元组数达到 2000 万才会触发一次 VACUUM。在这个“等待期”里表已经膨胀得非常厉害死元组还会拖慢所有索引扫描。对于大表我倾向于把这个阈值改小ALTER TABLE orders SET (autovacuum_vacuum_scale_factor 0.05); ALTER TABLE orders SET (autovacuum_vacuum_threshold 1000);这样每积累 5% 左右的死元组就会触发一次清理比默认值更及时。用粒度的存储参数来控制热点表比全局把 scale_factor 调低更精准因为小表频繁触发没必要大表又不希望等太久。还需要观察 autovacuum 是否真的跑完。pg_stat_user_tables 视图里有 last_autovacuum、autovacuum_count、n_dead_tup 这几个字段。如果 n_dead_tup 长期大于 0 且 last_autovacuum 很长时间没更新那就要检查是不是 autovacuum worker 被长事务阻塞了或者 maintenance_work_mem 给太小导致清理太慢。4.2 表膨胀和索引膨胀量化之后才知道该不该重建膨胀是一个典型的“温水煮青蛙”问题。表面看表行数没变但实际占用空间越来越大最终导致读取的磁盘块变多缓存命中率下降。判断表是否膨胀可以用一个朴素的方法对比表的实际大小和 pg_class.relpages 估算大小差异过大说明膨胀严重。更精确的诊断可以安装 pgstattuple 扩展CREATE EXTENSION IF NOT EXISTS pgstattuple; SELECT * FROM pgstattuple(orders);这个函数会返回 dead_tuple_count、free_space 等指标能直接看出表的健康程度。索引膨胀同样常见。比如频繁的更新操作会让索引页分裂产生大量空页索引体积膨胀到实际数据的好几倍。处理方式一般是重建索引REINDEX TABLE orders; REINDEX INDEX idx_orders_created_at;REINDEX 会锁表生产环境需要谨慎。如果无法接受长时间锁可以使用 pg_repack 这类工具在线重建但一定要先在测试环境演练过。另一个技巧是定期重建某些高频更新的索引比如把重建操作放到业务低峰期能显著改善查询稳定性。5. 监控与基线调优效果必须可衡量5.1 值得长期盯住的五个指标调优结束后如果不持续观察等于白干。我会把以下五个指标的曲线作为后续回归分析的依据第一个是数据库的整体命中率也就是 shared hit 和 disk read 的比例。命中率长期低于 90%说明内存配置或索引质量有较大问题。第二个是单条核心 SQL 的平均耗时与 p99 耗时这个能直观反映查询稳定性。第三个是活跃连接数和等待事件分布特别是 Lock、IO 和 CPU 的占比变化。第四个是 WAL 生成速率和 checkpoint 频率这个能反映写入负载的波动。第五个是死元组和膨胀趋势提前预示维护操作是否及时。这些指标不需要自己造轮子PostgreSQL 自带的视图加定期快照就够用。我习惯每隔一小时把 pg_stat_database、pg_stat_user_tables、pg_stat_statements 的输出存到一张统计表里后续用 SQL 分析趋势。你甚至可以写一个简单的定时任务把快照导出到 CSV再导入分析工具。5.2 压测回归如何验证调优真的有效调优验证不能只靠生产流量自然验证尤其是改动比较大的参数最好先用压测工具做一轮回归。pgbench 是 PostgreSQL 自带的压测工具虽然它的场景比较基础但用来对比参数调整前后的极限吞吐量是完全够用的。比如我要验证连接数和 work_mem 改动的影响可以这样跑pgbench -h 127.0.0.1 -p 5432 -U pguser -c 16 -j 4 -T 120 -f read_only.sql mydb-c 是并发连接数-j 是线程数-T 是运行秒数-f 是指定脚本。跑出来的 TPS 和平均延迟要记录保存。同样的测试脚本调整参数之后跑一次对比 TPS 和延迟分布。如果 TPS 提升但 p99 延迟波动变大那也不能算成功因为业务更在意的是长尾延迟。如果业务场景复杂只有 pgbench 不够我建议准备一套和生产查询特征一致的 SQL 集合至少覆盖高频查询、写入、批量任务三类把它变成一个回归压测脚本。这套东西的价值会随着每次调优逐渐显现到后期比任何参数文档都有用。6. 常见问题速查与避坑记录6.1 十个容易踩的坑以下是我在多次 PostgreSQL 调优实战中遇到的高频问题整理成了一张速查表建议收藏现象常见原因快速处理建议CPU 高但 IO 低SQL 慢执行计划选择了低效算法检查是否缺少索引、统计信息过期CPU 不高但查询很慢锁等待或 IO 等待看 pg_stat_activity 的 wait_event改参数后没生效参数需要重启或 reload用 pg_settings 查 pending_restart磁盘 IO 有规律的尖峰checkpoint 集中刷脏页调整 max_wal_size、checkpoint_timeout表查询越来越慢表膨胀或死元组堆积检查 n_dead_tup及时 VACUUM 或 REINDEX全表扫描但行数很少统计信息不准ANALYZE 后再看执行计划连接池被占满连接泄漏或长事务检查 idle in transaction、锁等待WAL 目录增长过快归档失败或 max_wal_size 太小检查 pg_stat_archiver、磁盘空间同一 SQL 不同参数值性能差异大数据分布不均绑定变量固定计划不合适评估 force_custom_plan 或优化统计信息含有索引的更新很慢索引过多导致维护成本高删除冗余索引合并索引其中参数不生效的问题我多说一句不要只看 postgresql.conf 文件。用SELECT name, setting, pending_restart, context FROM pg_settings WHERE name IN (shared_buffers, work_mem, max_wal_size);context 字段如果显示 postmaster表示需要重启如果显示 sighup表示只需要 reload如果显示 user表示你可以在会话里直接 SET。理解了这三类就不会出现“明明改了配置却没反应”的迷惑。6.2 一个慢查询排查案例最后用一个案例把这套清单串起来。某业务库反馈订单查询接口持续变慢从原来的平均 80ms 涨到 1200ms。我按清单一步步做先看 pg_stat_statements发现慢的主要是两张表关联的查询calls 很高total_exec_time 排名第一。接着看 pg_stat_user_tables发现其中一张大表的 n_dead_tup 达到 1800 万last_autovacuum 已经是一周前明显是 autovacuum 跟不上。先用 ANALYZE 刷新统计信息确认执行计划里还是出现顺序扫描而且查询条件是对一个低基数字段做过滤选择性极差。在这个字段上建索引没有意义真正的问题是存在大量死元组导致每个顺序扫描都要读大量无效页面。于是给这张表单独设置了更小的 autovacuum 参数并做了一次手动 VACUUM然后 REINDEX 了相关索引之后接口平均耗时回落到 90ms。这个案例说明了一个核心观点性能问题往往不是单一因素而是缓存、维护、索引设计共同作用的结果。完整的检查清单就是避免你只修了其中一个齿轮而漏掉另一个正在崩坏的齿轮。对我个人来说这套调优流程的宝贵之处不在于某个参数有多精妙而在于它让我在每一次故障排查时都保持同样的节奏先量数据再定位瓶颈然后最小化改动最后用指标验证。如果你正准备做一次系统性的 PostgreSQL 性能调优建议就从建立基线开始别急于改参数。等你把这份清单走完一轮再看那些数据库已经出现的症状多半都能找到头绪。